CODEWITHSUNDEEP
mysqlsql
ShadowAccount
ShadowAccount

Reputation: 321

Subquery inside of Group By?

Here's an example that's working, but not getting everything I need:

SELECT *, 
       SUM(price) AS total_revenue 
FROM payments 
GROUP BY DATE(timestamp)

That's getting the total revenue (adding price), but I also need to get the total price where vendor = '22' AND credited = 'Vendor' (like I did with price, on a per day basis).

Here's what I tried:

SELECT *, 
       SUM(price) AS total_revenue, 
       (SELECT SUM(price) WHERE vendor = '22' AND credited = 'Vendor') AS vendor_revenue 
FROM payments 
GROUP BY DATE(timestamp)

What am I doing wrong or is there any way to do it?

Upvotes: 0

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Your original query is malformed -- you have select * with group by.

SELECT DATE(p.timestamp), SUM(price) AS total_revenue,
       SUM(CASE WHEN vendor = 22 AND credited = 'Vendor' THEN price ELSE 0 END) as vendor_22_price
FROM payments p
GROUP BY DATE(p.timestamp)

Upvotes: 1

Nick
Nick

Reputation: 147266

Conditional aggregation is the best way to achieve what you want:

SELECT
    DATE(timestamp) AS ts,
    SUM(price) AS total_revenue,
    SUM(CASE WHEN vendor = '22' AND credited = 'Vendor' THEN price ELSE 0 END) AS vendor_revenue
FROM payments
GROUP BY DATE(timestamp);

Upvotes: 4

Related Questions