Produce summary line from multiple detail lines

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.


Thanks in advance ....
raymurphyAsked:
Who is Participating?
 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
0
 
Simone BSenior E-Commerce AnalystCommented:
Have you tried something like this:

select SupplierNAme, SupplierNo, InvoiceDate, Sum(InvoiceAmount) as SumInvoiceAmount
FROM suppliers
GROUP BY SupplierNAme, SupplierNo, InvoiceDate
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Simone BSenior E-Commerce AnalystCommented:
Sorry, that won't work. I missed your last spec showing the layout.
0
 
raymurphyAuthor Commented:
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
 
raymurphyAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.