Menu
I'm trying to get 'Cash', 'Check' and 'Credit Card' totals in new columns based on ProductID from the same table.
Table - Payments
+-----------+------------+---------------+--------+ | ProductID | SaleDate | PaymentMethod | Amount | +-----------+------------+---------------+--------+ | 3 | 2012-02-10 | Cash | 10 | | 3 | 2012-02-10 | Cash | 10 | | 3 | 2012-02-10 | Check | 15 | | 3 | 2012-02-10 | Credit Card | 25 | | 4 | 2012-02-10 | Cash | 5 | | 4 | 2012-02-10 | Check | 6 | | 4 | 2012-02-10 | Credit Card | 7 | +-----------+------------+---------------+--------+ Desired Output -
+------------+------+-------+-------------+-------+ | ProductID | Cash | Check | Credit Card | Total | +------------+------+-------+-------------+-------+ | 3 | 20 | 15 | 25 | 60 | | 4 | 5 | 6 | 7 | 18 | +------------+------+-------+-------------+-------+ I've tried LEFT JOINing the same table but haven't had any success. Any suggestions would be appreciated. Thanks.
Unsuccessful and incomplete attempt -
SELECT P.ProductID, Sum( PCash.Amount ) AS 'Cash', SUM( PCheck.Amount ) AS 'Check', SUM( PCredit.Amount) AS 'Credit Card' FROM Payments AS P LEFT JOIN Payments AS PCash ON P.ProductID = PCash.ProductID AND PCash.PaymentMethod = 'Cash' LEFT JOIN Payments AS PCheck ON P.ProductID = PCheck.ProductID AND PCheck.PaymentMethod = 'Check' LEFT JOIN Payments AS PCredit ON P.ProductID = PCredit.ProductID AND PCredit.PaymentMethod = 'Credit' WHERE P.SaleDate = '2012-02-10' GROUP BY ProductID; 
293
SELECT CONCAT('SELECT ', group_concat(`COLUMN_NAME` SEPARATOR '+'), ' FROM scorecard') FROM `INFORMATION_SCHEMA`. `COLUMNS` WHERE `TABLE_SCHEMA` = (select database()) AND `TABLE_NAME` = 'scorecard' AND `COLUMN_NAME` LIKE 'mark%'; The query above will generate another query that will do the selecting for you.
To sum rows with same ID, use the GROUP BY HAVING clause.
Example: MySQL SUM() function using multiple columnsMySQL SUM() function retrieves the sum value of an expression which is made up of more than one columns. The above MySQL statement returns the sum of multiplication of 'receive_qty' and 'purch_price' from purchase table for each group of category ('cate_id') .
The order that rows are returned in is guaranteed ONLY by ORDER BY clause (or in MySQL, an ORDER BY implicitly specified in the GROUP BY clause.) Apart from that, there is NO GUARANTEE of the order rows will be returned in. Apart from that, MySQL is free to return the rows in any sequence.
I think you're making this a bit more complicated than it needs to be.
SELECT ProductID, SUM(IF(PaymentMethod = 'Cash', Amount, 0)) AS 'Cash', -- snip SUM(Amount) AS Total FROM Payments WHERE SaleDate = '2012-02-10' GROUP BY ProductID If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
