SUM price with nested select

Question

I have this table here:

total_price  date_buy                number_buy
$$ 299.00    18/06/2014 12:59:34 PM  1
$$ 299.00    18/06/2014 12:59:34 PM  1
$$ 76.00     21/06/2014 16:22:12 AM  2

All I'm trying to do is SUM the total_price grouped by date_buy (month) and number_buy, for this I try this code:

select SUM(CAST(substr(total_price, 4) as FLOAT)) as Total, substr(date_buy, 4, 2) as Month FROM myDatabase WHERE substr(date_buy, 7, 4) = '2014' group by substr(date_buy, 4, 2) ORDER BY substr(date_buy, 4, 2)

This code works great and he give me the SUM of this values:

299.00
299.00
76.00
______

1152.00

But as I said earlier , I want to accomplish the sum grouping date_buy (month) and number_buy, so.. The correct Sum are:

299.00
76.00
______

375.00

I believe that I should have a subquery (nested select), in place of the command: SUM(CAST(substr(total_price, 4) as FLOAT)) as Total that will bring me the SUM of total_price grouped by number_buy, and and so I could continue with the rest of the code, Right?

If no, How i can SUM the total_price grouped by date_buy and number_buy? Else how I can create that nested select?

@andrewdleach, the table have more than this 3 columms, one of the columns are called product that have two different products. — LettersBa, Sep 13 '15 at 15:57

score 1 · Accepted Answer · edited May 23 '17 at 12:22

You can use your both columns in your group by clause to group depend up on the two columns you require. Try the below thing.

select SUM(CAST(substr(total_price, 4) as FLOAT)) as Total, substr(date_buy, 4, 2) as Month FROM myDatabase WHERE substr(date_buy, 7, 4) = '2014' group by substr(date_buy, 4, 2), number_buy ORDER BY substr(date_buy, 4, 2)

You can refer to the below page for detailed explanation about groupby clause. Using group by on multiple columns

SUM price with nested select

1 Answers1