Multiple Sum Columns per Row
I am working out of our sales table in DB and trying to put together a query that returns the following:
Customer 2010 Sales 2011 Sales 2012 Sales
Each customer may have any number of sales in a given year. I have it set up currently that it provides three rows per customer, with a different years sales in each row (using SUM() and GROUP BY YEAR()) but this is more difficult for comparison.
Is there any way to create a single query which returns aggregates in multiple columns per row?
My current code:
SELECT
Cu.CustomerName, Cu.SalespersonN,
SUM(ART.SalesAnalysis)AS 'Sales', YEAR(PostingDate) AS 'Year'
FROM ARTransaction AS ART
JOIN Customer AS Cu
ON ART.CustomerN = Cu.CustomerN
WHERE YEAR(PostingDate) >= '2010'
AND YEAR(PostingDate) <= '2012'
AND Cu.SalespersonN <> 0
GROUP BY Cu.SalespersonN, Cu.CustomerName, YEAR(PostingDate)
ORDER By Cu.CustomerName, YEAR(PostingDate)
Customer 2010 Sales 2011 Sales 2012 Sales
Each customer may have any number of sales in a given year. I have it set up currently that it provides three rows per customer, with a different years sales in each row (using SUM() and GROUP BY YEAR()) but this is more difficult for comparison.
Is there any way to create a single query which returns aggregates in multiple columns per row?
My current code:
SELECT
Cu.CustomerName, Cu.SalespersonN,
SUM(ART.SalesAnalysis)AS 'Sales', YEAR(PostingDate) AS 'Year'
FROM ARTransaction AS ART
JOIN Customer AS Cu
ON ART.CustomerN = Cu.CustomerN
WHERE YEAR(PostingDate) >= '2010'
AND YEAR(PostingDate) <= '2012'
AND Cu.SalespersonN <> 0
GROUP BY Cu.SalespersonN, Cu.CustomerName, YEAR(PostingDate)
ORDER By Cu.CustomerName, YEAR(PostingDate)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Something I do to get around this is label the columns year0, year1, ... where year0 is current, year1 is one year AGO, etc. This way the column labels never change as the data effectively scrolls across beneath them.
Open in new window
I've generalised a fragment from the query that produced the results above - I'm interested in days, not years, but is good for minutes, hours, days, months, quarters, years. (Weeks is a little problematic with the beginning of the week and all that.)To summarise - the nice dynamic labels are hard to code, and stuff that is hard to code, is hard to maintain, and hard to modify. I've coded something a little easier to read, and only need a small amount of effort in training my users etc in how to read the results.
HTH
David