looper8
asked on
Totalling in T-SQL
On SQL Server 2000 this query:
select distinct c.c11_number AS [C11 Number],
g.cost, g.motability_total, g.amount_outstanding,
s.name AS [Name], s.supplier_id AS [Supplier ID]
from c11 c
left join grants g on c.c11_number = g.c11_number
left join adaptations a on a.grant_id = g.grant_id
left join suppliers s on s.supplier_id = a.supplier_id
Gives results like this:
100075 .00 494.00 NULL NULL NULL
100075 150.00 150.00 NULL ALFRED BEKKER LTD ABC
100075 385.00 385.00 NULL ALFRED BEKKER LTD ABC
What I need to do is when rows are identical except for the monetary values is add up those values. In other words I would want:
100075 .00 494.00 NULL NULL NULL
100075 535.00 535.00 NULL ALFRED BEKKER LTD ABC
How do I do that?
select distinct c.c11_number AS [C11 Number],
g.cost, g.motability_total, g.amount_outstanding,
s.name AS [Name], s.supplier_id AS [Supplier ID]
from c11 c
left join grants g on c.c11_number = g.c11_number
left join adaptations a on a.grant_id = g.grant_id
left join suppliers s on s.supplier_id = a.supplier_id
Gives results like this:
100075 .00 494.00 NULL NULL NULL
100075 150.00 150.00 NULL ALFRED BEKKER LTD ABC
100075 385.00 385.00 NULL ALFRED BEKKER LTD ABC
What I need to do is when rows are identical except for the monetary values is add up those values. In other words I would want:
100075 .00 494.00 NULL NULL NULL
100075 535.00 535.00 NULL ALFRED BEKKER LTD ABC
How do I do that?
you could also use a compute by clause
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys!
Jimhorn: doesn't seem to do the totalling up.
Basile: how?
KarinLoos: Looks good at first glance (I needed to add a.name and a.supplier_id into the GROUP BY), but I need to check it out a bit more and will get back to you.
Jimhorn: doesn't seem to do the totalling up.
Basile: how?
KarinLoos: Looks good at first glance (I needed to add a.name and a.supplier_id into the GROUP BY), but I need to check it out a bit more and will get back to you.
select c.c11_number AS [C11 Number],
SUM(g.cost), g.motability_total, g.amount_outstanding,
s.name AS [Name], s.supplier_id AS [Supplier ID]
from c11 c
left join grants g on c.c11_number = g.c11_number
left join adaptations a on a.grant_id = g.grant_id
left join suppliers s on s.supplier_id = a.supplier_id
GROUP BY c.c!!_number, g.motability_total, g.amount_outstanding, s.name, s.supplier_id