HalfPintBoy HalfPintBoy - 4 months ago 12
SQL Question

Subqueries for aggregation needs in sql

I have a table like this:

Id Date Price Item Type
1 2009-09-21 25 1 M
2 2009-08-31 16 2 T
1 2009-09-23 21 1 M
2 2009-09-03 12 3 T

I try to receive the output of ID and column of sum price mult items for type='M' and another column with same logic for type='T'

Only way how to do it for me is using multi-cte but it is kind of complex and big:

with cte as (
select distinct a.id, a.date
sum(price*a.item) as numm
from table a
where a.type='M'
group by a.id),

crx as (
select cte.id, cte.numm, sum(a.price*a.item) as numm_1 from cte
join table a on a.id=cte.id and a.date=cte.date
where a.type='T'
group by cte.id)

select * from crx

Having a certain feeling that it can be done better (for example using subqueries)-asking you how can it be done.


SQLlite stuff would be greatly appreciated!



Perhaps this will help

Declare @YourTable table (Id int,Date date,Price money,Item int,Type varchar(25))
Insert into @YourTable values

Select ID
      ,sum(case when Type='M' then Price*Item else 0 end) as M
      ,sum(case when Type='T' then Price*Item else 0 end) as T
 From  YourTable
 Group By ID


ID  M       T
1   46.00   0.00
2   0.00    68.00