Hey all. Ive got a big problem with an sql statement Im working on.
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. Thanks 5 2248
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
HTH, jens Suessmeyer.
Jens (Je**@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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/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 :) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: george lewycky |
last post by:
Hi everyone
How can I have 2 or more SQL statements run sequentially.
This is needed to synchronize a reference table which is later
used in a PL/SQL program that I wrote also.
I'm trying...
|
by: JackT |
last post by:
Hi,
I have the following SQL
SELECT Table1.Col1, Table3.Col1 AS Expr1,
COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,
FROM Table3
INNER JOIN Table2 ON...
|
by: Chris Mullins |
last post by:
I've spent a bit of time over the last year trying to implement RFC 3454
(Preparation of Internationalized Strings, aka 'StringPrep').
This RFC is also a dependency for RFC 3491...
|
by: jack daniels via .NET 247 |
last post by:
Hi! Perhaps you know of a better way here.
I have an Access database, actually a single table. Now, at the click of a button on a C# Form, each and every single cell associated with the primary...
|
by: alwayswinter |
last post by:
I currently have a form where a user can enter results from a genetic
test. I also have a pool of summaries that would correspond to
different results that a user would enter into the form. I...
|
by: Barry |
last post by:
Hi all,
I've noticed a strange error on my website. When I print a capital
letter P with a dot above, using & #7766; it appears correctly, but
when I use P& #0775 it doesn't. The following...
|
by: SomeDude |
last post by:
Lo group,
I am wondering if there is a way of combining two SELECT statements into
a single query.
Here's the obligatory example to clarify things:
SELECT id WHERE name=mike
SELECT bills...
|
by: ravi |
last post by:
I have created the following interest to calculate the interest for
the following currency pairs. I have tried to combine them in macros
using conditions but the next query that is run in the macro...
|
by: isdeveloper |
last post by:
Hi All,
I have a problem with a table that I want to get nice data out of in a
single query. The guys here reckon it can't be done in a single query
but I wanted to prove them wrong !!...
|
by: Tristan Miller |
last post by:
Greetings.
Is it possible using HTML and CSS to represent a combining diacritical mark
in a different style from the letter it modifies? For example, say I want
to render Å‘ (Latin small letter...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |