Totalling in T-SQL

looper8
looper8 used Ask the Experts™
on
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?



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Give this a whirl...

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
Auerelio VasquezETL Developer

Commented:
you could also use a compute by clause
SELECT   a.c11_number  AS  [C11 Number],
              sum ( a.cost ) as COST,
              sum( a.motability_total) as motability_total,
              sum ( a.amount_outstanding ),
             a.name AS [Name],
             a.supplier_id AS [Supplier ID]
FROM
(
select distinct c.c11_number ,
g.cost, g.motability_total, g.amount_outstanding,
s.name AS [Name], s.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
)  A
GROUP BY  a.c11_number  
Your example shows the number, name, and supplier combination as unique; therefore, they are what you group by and sum the other fields.  A sum of nulls yields Nulls and if I understand your situation ...

select c.c11_number AS [C11 Number],
SUM(g.cost) AS Cost, SUM(g.motability_total) AS motability_total, SUM(g.amount_outstanding) AS 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.c11_number, s.name, s.supplier_id

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial