Solved

# Produce summary line from multiple detail lines

Posted on 2012-08-17
644 Views
Need to produce a summary line from multiple detail lines ...

Sample data

SELECT SupplierName, SupplierNo,  InvoiceDate, InvoiceAmount
FROM suppliers

SupplierName  SupplierNo   InvoiceDate   InvoiceAmount
MCDONALD      746793           2012.01       -70
MCDONALD      746793           2012.01       -35
MCDONALD      746793           2012.01       -70

MCDONALD      746793           2012.02       -60
MCDONALD      746793           2012.02       -345
MCDONALD      746793           2012.02       -402

MCDONALD      746793           2012.03       -128
MCDONALD      746793           2012.03       -93
MCDONALD      746793           2012.03       -93

So the total for 2012.01 is -175, the total for 2012.02 is -807 and the total for 2012.03 is -314.

With the live data, there can be any number of values for InvoiceDate (eg 2011.01 ...2011.12,  2012.01 .... 2012.07 etc), and many different SupplierNames ..

Need to produce a summary line from the detail lines above, showing

SupplierName  SupplierNo    2012.01   2012.02   2012.03   YTD-Total
MCDONALD      746793          -175        -807          -314         -1296

What I've been trying so far is this :

SELECT SupplierName, SupplierNo,
SUM(CASE WHEN InvoiceDate = '2012.01' THEN InvoiceAmount ELSE 0 END) AS [2012.01],
SUM(CASE WHEN InvoiceDate = '2012.02' THEN InvoiceAmount ELSE 0 END) AS [2012.02],
SUM(CASE WHEN InvoiceDate = '2012.03' THEN InvoiceAmount ELSE 0 END) AS [2012.03],
SUM(InvoiceAmount) AS [YTD-Total]
FROM suppliers
AND InvoiceDate IN ('2012.01', '2012.02', '2012.03')
GROUP BY SupplierNAme, SupplierNo, InvoiceDate

But that didn't produce the required summary line but instead produced :

SupplierName  SupplierNo    2012.01   2012.02   2012.03   YTD-Total
MCDONALD      746793          -175         0      0             -175
MCDONALD      746793          0              -807      0      -807
MCDONALD      746793          0              0      -314      -314

With the live data, there can be any number of values for InvoiceDate (eg 2011.01 ...2011.12, 2012.01 .... 2012.07 etc), so I'd need to pick up InvoiceDate up directly rather than my current use of AND InvoiceDate IN ('2012.01', '2012.02', '2012.03') ...

Can anyone help me to come up with the best SQL statement to produce the summary line I need, ie from the sample data shown :

SupplierName  SupplierNo    2012.01   2012.02   2012.03   YTD-Total
MCDONALD      746793          -175        -807          -314         -1296

The target database is MS SQL 2005.

0
Question by:raymurphy

LVL 92

Expert Comment

0

LVL 11

Expert Comment

Have you tried something like this:

select SupplierNAme, SupplierNo, InvoiceDate, Sum(InvoiceAmount) as SumInvoiceAmount
FROM suppliers
GROUP BY SupplierNAme, SupplierNo, InvoiceDate
0

LVL 11

Expert Comment

Sorry, that won't work. I missed your last spec showing the layout.
0

Author Comment

I'll have a look at that link, Patrick, so thanks for that - in the meantime I've realised that my GROUP BY was wrong, and shouldn't have been GROUPING on InvoiceDate  - if I change the SQL to read :

SELECT SupplierName, SupplierNo,
SUM(CASE WHEN InvoiceDate = '2012.01' THEN InvoiceAmount ELSE 0 END) AS [2012.01],
SUM(CASE WHEN InvoiceDate = '2012.02' THEN InvoiceAmount ELSE 0 END) AS [2012.02],
SUM(CASE WHEN InvoiceDate = '2012.03' THEN InvoiceAmount ELSE 0 END) AS [2012.03],
SUM(InvoiceAmount) AS [YTD-Total]
FROM suppliers
WHERE SupplierName = 'MCDONALD'
AND InvoiceDate IN ('2012.01', '2012.02', '2012.03')
GROUP BY SupplierName, SupplierNo

then I do get the single summary line as expected. Given that, is there a way I can modify this SQL statement so that it can pick up ANY InvoiceDate from my table ?
0

LVL 92

Accepted Solution

Yes, but you would have to use dynamic SQL to do it.  Which is why I recommend mark_wills's article, as the procedure he outlines there does all the donkey work for you :)
0

Author Closing Comment

Just began reading that article and can see that it's going to be very relevant to what I need, so thanks a lot for that pointer .....
0

## Featured Post

### Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed