MySQL-এ দুই কলাম গুণ করে গ্রুপ বাই সমষ্টি (SUM) বের করা

MySQL-এ দুটি কলাম গুণফল (quantity × price) গ্রুপ বাই করে মোট যোগফল বের করতে SUM() এগরিগেট ফাংশন ব্যবহার করা হয়।

টেবিল তৈরি এবং ডেটা ইনসার্ট

mysql> CREATE TABLE DemoTable (
    -> CustomerId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> CustomerProductName VARCHAR(100),
    -> CustomerProductQuantity INT,
    -> CustomerPrice INT
    -> );

mysql> INSERT INTO DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) VALUES('Product-1', 5, 400);
mysql> INSERT INTO DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) VALUES('Product-2', 3, 100);
mysql> INSERT INTO DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) VALUES('Product-1', 2, 300);
mysql> INSERT INTO DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) VALUES('Product-1', 5, 50);
mysql> INSERT INTO DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) VALUES('Product-3', 6, 10);
mysql> INSERT INTO DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) VALUES('Product-2', 10, 20);

সব রেকর্ড দেখুন

mysql> SELECT * FROM DemoTable;

+------------+---------------------+-------------------------+---------------+
| CustomerId | CustomerProductName | CustomerProductQuantity | CustomerPrice |
+------------+---------------------+-------------------------+---------------+
|          1 | Product-1           |                       5 |           400 |
|          2 | Product-2           |                       3 |           100 |
|          3 | Product-1           |                       2 |           300 |
|          4 | Product-1           |                       5 |            50 |
|          5 | Product-3           |                       6 |            10 |
|          6 | Product-2           |                      10 |            20 |
+------------+---------------------+-------------------------+---------------+

প্রোডাক্ট বাই গ্রুপ করিয়ে টোটাল প্রাইস

mysql> SELECT CustomerProductName, 
    -> SUM(CustomerProductQuantity * CustomerPrice) AS TOTAL_PRICE 
    -> FROM DemoTable 
    -> GROUP BY CustomerProductName;

ফলাফল

+---------------------+-------------+
| CustomerProductName | TOTAL_PRICE |
+---------------------+-------------+
| Product-1           |        2850 |
| Product-2           |         500 |
| Product-3           |          60 |
+---------------------+-------------+

গণনা:

Product-1: (5×400) + (2×300) + (5×50) = 2000 + 600 + 250 = 2850
Product-2: (3×100) + (10×20) = 300 + 200 = 500
Product-3: (6×10) = 60