How to construct a SQL query with different aggregation

I have the following data:

 Date   ID Type Cost
 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

I want a query that shows

Date       Type CostPercentage
May 01    A    (1+2) / (1+2+3+4)
May 01    B    (3+4) / (1+2+3+4)
May 02    A    (5+6) / (5+6+7+8)
May 02    B    (7+8) / (5+6+7+8)

What I currently have is

select Date, Type, sum(cost)
from mytable
group by Date, Type

I would really want to show the percentage,开发者_如何学运维 but that will involve deviding another number which is itself an aggregation.

How am I suppose to do that?

EDITED to reflect my real problem. I over simplified it before.

---

You could use a subquery:

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

Link to working example @odata.

---

This query should work as desired, "o" is the outer table, "i" the inner table:

select  o.date,
        o.type,
        sum(o.cost) / (select sum(i.cost) from mytable as i where i.date=o.date) * 100.0
    from    mytable as o
    group by 
            o.date,o.type

For this sample data:

Day1, A, 1
Day2, A, 2
Day1, B, 3
Day2, B, 4

the results are:

• Type A costs made up for 25% of the Day1 total costs (1 / 1+3).
• Type B costs made up for 75% of the Day1 total costs (3 / 1+3).
• Type A costs made up for ~33% of the Day2 total costs (2 / 2+4).
• Type B costs made up for ~66% of the Day2 total costs (4 / 2+4).

---

select 
  mytable.date, mytable.type, sum(mytable.cost)/day.total_cost
from mytable
inner join
(select date, sum(cost) as total_cost from mytable group by date) as day
on day.date=mytable.date
group by mytable.date, mytable.type