Joins and Grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joins and Grouping

Author

Topic

l8sk8r
Starting Member

1 Post

Posted - 2010-11-09 : 17:10:37

Howdy all, SQL rookie here.

I'm trying to get to some to data from our MS SQL server as our CRM front-end is laughably bad.
I can sort of get what I want but I'm having a few issues with the joins and way the info is grouped

Effectively I have 3 tables:

i = which contains invoice info
t = which contains transactions
ty = which contains type ID

Because all transactions have a type ID, the only way I can get to them is by summing all instances for each type for every invoice as shown in the below code.


SELECT       i.invoiceid AS [Inv No], CASE WHEN ty.typeID = '1' THEN SUM(t.TotalExclVAT) ELSE '0' END AS Fees, 
                      CASE WHEN ty.typeID = '2' THEN SUM(t.TotalExclVAT) ELSE '0' END AS Payments, 
                      CASE WHEN ty.typeID = '3' THEN SUM(t.TotalExclVAT) ELSE '0' END AS Adjustments, 
                      CASE WHEN ty.typeID = '4' THEN SUM(t.TotalExclVAT) ELSE '0' END AS Expenses, ty.typeID AS [Type ID]
FROM         dbo.transactions AS t INNER JOIN
                      dbo.typelist AS ty ON t.transcodeID = ty.transcodeID LEFT OUTER JOIN
                      dbo.invoices AS i ON t.invoiceID = i.invoiceID
WHERE     (t.TotalExclVAT <> 0)
GROUP BY i.invoiceid, ty.typeID

This however produces a data set which looks like:


Inv No            Fees        Payments       Adjustments       Expenses         Type ID
101234            0.00       -100.00         0.00              0.00             2
101234            150.00      0.00           0.00              0.00             1
101234            0.00        0.00          -50.00             0.00             3

It can obviously can be manipulated in Pivot in excel, but ideally i need it in a tabular form where the sum of each of these transaction types are shown against the specific invoice on one line.


Inv No            Fees        Payments       Adjustments       Expenses
101234            150.00       -100.00       -50.00            0.00

Any clues on how best to do this would be much appreciated

Cheers!

Danny

/
----
OOOO

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-09 : 17:22:21

Try doing SUM(CASE WHEN...) AS columnname for each column.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-09 : 17:24:14

Yeah, and remove the typeid from your GROUP BY:


SELECT       i.invoiceid AS [Inv No], 
		sum(CASE WHEN ty.typeID = '1' THEN SUM(t.TotalExclVAT) ELSE '0' END) AS Fees, 
		sum(CASE WHEN ty.typeID = '2' THEN SUM(t.TotalExclVAT) ELSE '0' END) AS Payments, 
		sum(CASE WHEN ty.typeID = '3' THEN SUM(t.TotalExclVAT) ELSE '0' END) AS Adjustments, 
		sum(CASE WHEN ty.typeID = '4' THEN SUM(t.TotalExclVAT) ELSE '0' END) AS Expenses, ty.typeID AS [Type ID]
FROM         dbo.transactions AS t INNER JOIN
                      dbo.typelist AS ty ON t.transcodeID = ty.transcodeID LEFT OUTER JOIN
                      dbo.invoices AS i ON t.invoiceID = i.invoiceID
WHERE     (t.TotalExclVAT <> 0)
GROUP BY i.invoiceid

Be One with the Optimizer
TG

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-09 : 17:25:43

whoops, missed that part. Good call, TG.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-09 : 17:34:37

The OP seems like a smart guy - he would have figured it out

But now I also see that he has to remove ty.typeid from the SELECT:

--, ty.typeID AS [Type ID]

I hate when people tack on a column on the same line as other columns :(

Be One with the Optimizer
TG

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-09 : 17:41:29

This is a mess! There is no such thing as a type_id-- an attribute can be a < something >_type or a < something>_id but both.

1) Pull the "type_id" out of the GROUP BY
2) put the CASE inside the SUM()

SUM (CASE WHEN transaction_type = '2'
THEN (texcl_vat) ELSE 0.00
END AS payment_tot

I just had to fix those data element names. Read ISO-11179 naming rules. And isn't VAT a decimal and not a string?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources