There are 2 tables with a master/detail relationship. The Header Table
is the master, the Line Table is the detail. So for each Header, there
are many Lines, but a Line can only reference one Header.
There is a Line Total and Line Cost in each Line Record. Each Line
Record has a type.
What I want to be able to do is, for each Header, I want to Sum each
corresponding Line's Total and Cost where the type is either one value
or another. If the type is, for example, 10, only sum the Total, if its
type 2, only sum the Cost.
Therefore, after the query is executed, you should have a result set
something like this
Job : Job1 (header id)
Desc : Job0001 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job1)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job1)
--------------------------------
Job : Job2 (header id)
Desc : Job0002 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job2)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job2)
--------------------------------
etc.
Hope this makes sense. ThanksTry 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
HTH, jens Suessmeyer.|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You are right, thats not even better that was missing in my example.|||Is there a way to say
SUM(CASE Line_type WHEN 10 THEN costs else 0 END),
SUM(CASE Line_type WHEN NOT 10 THEN Totals else 0 END) ?|||Its OK, I figured it out.
I ended up using
SUM(CASE WHEN Line_type = 10 THEN costs else 0 END),
SUM(CASE WHEN Line_type <> 10 THEN Totals else 0 END)
THanks for the help guys :)
