Posted by Erland Sommarskog on 01/10/06 00:29

Jens (Jens@sqlserver2005.de) writes:

> Try this one here:
>
> Select
> header_id,
> header_desc,
> SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
> SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
> FROM headers
> INNER JOIN
> line
> ON line.header_id = header.header_id

Better:

SELECT h.header_id, h.header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers h
JOIN line ON l.header_id = h.header_id
GROUP BY h.header_id, h.header_desc

Particularly that GROUP BY clause is quite important...


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]