How to construct a SQL query with different aggregation

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/5244837/how-to-construct-a-sql-query-with-different-aggregation

Stack Overflow

Q&A for professional and enthusiast programmers

-- How to construct a SQL query with different aggregation
-- http://stackoverflow.com/questions/5244837/how-to-construct-a-sql-query-with-different-aggregation


declare @t table ([Date] varchar(10), ID char(1), Type char(1), Cost money)
insert @t values
 ('May 01',  'a',  'A',    1),
 ('May 01',  'b',  'A',    2),
 ('May 01',  'c',  'B',    3),
 ('May 01',  'd',  'B',    4),
 ('May 02',  'e',  'A',    5),
 ('May 02',  'f',  'A',    6),
 ('May 02',  'g',  'B',    7),
 ('May 02',  'h',  'B',    8)
 
 
select  dt.Date
,       dt.Type
,       SUM(dt.Cost) as DayTypeTotal
,       (select SUM(Cost) from @t d where d.Date = dt.Date) as DayTotal
,       100.0 * SUM(dt.Cost) / 
             (select SUM(Cost) from @t d where d.Date = dt.Date) as Percentage
from    @t dt
group by
        dt.Date
,       dt.Type

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results