Solved

Multiple Sum Columns per Row

Posted on 2013-01-25
4
725 Views
Last Modified: 2013-01-30
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)
0
Comment
Question by:mjburgard
4 Comments
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 75 total points
Comment Utility
You can do this using the PIVOT operator but it's not perfect.  The PIVOT operator expects you to hardcode the values of the field on which you're pivoting.  In your case that would be the year. Have a look at the examples through that link to get an understanding of how this works.

Hardcoding the year doesn't seem like the best way to go in this context. In order to avoid that, you could do the following.  First dump your data into a temporary table. Then use dynamic SQL to create the statement with the PIVOT operator, based on the values for the "Year" column in your temporary table.  It's a bit more work and no longer a single query, but it would work.  You could build a stored proc that does all this.

And to conclude, in case you happen to be using Reporting Services, you could simply use the Matrix control to pivot your data.  So if SSRS is an option, be sure to check it out!
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

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.
My pivot table resultsSo the 0, 1, 2 ... as column labels is fairly easy to generate
	select 
		st.SomeLabelOrSomething
		, datediff( day, st.SalesDate, getdate()) as DaysAgo
		, st.SalesTotalOrSoemthing
	from dbo.SomeTable st

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
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 75 total points
Comment Utility
Well, the first thing to consider is how are your users going to interact with the data.

If someting like Excel, or a report, or a screen, then those tools can often support a matrix or crosstab or tablix type function and can do all the hard work of transforming rows into columns.

Then you need to think about how to deliver the information.Does it have to be a "select" or can it be a Stored Procedure.

Because it is "year" then it isnt quite so bad to pivot, but still, the pivot function in SQL must explicitly know the new column names (as explicit literals) otherwise it wont pivot (some other DB's can derive that information and is very easy to pivot). Also being "year" you do have options to pivot (like a straight select case query).

To overcome having to know the columns, then strategies like dtodd works well "Current Year", "Last Year", "Prior Year" are nice generic columns and easy to cater for.

More importantly, they are then predictable column names so you can explicitly select "prior year" where as variable column names (like in a pivot) can create problems later when they change (though you do have a year to prepare, the difficulty is change of years and that transition from year to year).

So, first you need to ask yourself how are your users going to interact with the data, then you can work backwards from that to decide the best way to deliver the "raw" data.

But to answer your immediate request while you consider some of the above and previous postings, here is some code to play with :

-- the immediate need using PIVOT can be achieved with

SELECT *       
FROM  (SELECT Cu.Customername, Cu.SalespersonN, ART.salesanalysis, year(ART.postingdate) as yr
       FROM   ARTransaction AS ART
       JOIN   Customer AS Cu ON ART.CustomerN = Cu.CustomerN
       WHERE  YEAR(ART.PostingDate) >= '2010'
       AND    YEAR(ART.PostingDate) <= '2012'
       AND    Cu.SalespersonN <> 0 ) src
PIVOT
      (sum(salesanalysis) for yr in ([2010],[2011],[2012])) pvt


-- future proofing a little bit by extending the range of years, 
-- but then mucks up the select * (which isnt very good anyway)
-- note that you also have to change your YEAR selection as well 
-- which means as years progress, you are processing more and more data
-- unless you modify the query each time
 
SELECT *
FROM  (SELECT Cu.Customername, Cu.SalespersonN, ART.salesanalysis, year(ART.postingdate) as yr
       FROM   ARTransaction AS ART
       JOIN   Customer AS Cu ON ART.CustomerN = Cu.CustomerN
       WHERE  YEAR(ART.PostingDate) >= '2010'
--     AND    YEAR(ART.PostingDate) <= '2012'
       AND    Cu.SalespersonN <> 0 ) src
PIVOT
      (sum(salesanalysis) for yr in ([2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020])) pvt


-- you can create the above as a view then simply select from the view and is a bit better
-- creating the view is a one time task and can then be used whenever you want, 
-- a view is essentially a virtual table over the real data, more like a stored query.

create view vw_pivot_sales as
SELECT *
FROM  (SELECT Cu.Customername, Cu.SalespersonN, ART.salesanalysis, year(ART.postingdate) as yr
       FROM   ARTransaction AS ART
       JOIN   Customer AS Cu ON ART.CustomerN = Cu.CustomerN
       WHERE  YEAR(ART.PostingDate) >= '2010'
--     AND    YEAR(ART.PostingDate) <= '2012'
       AND    Cu.SalespersonN <> 0 ) src
PIVOT
      (sum(salesanalysis) for yr in ([2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020])) pvt
GO

-- selecting from the view makes it easier
select customername, salespersonn, [2010],[2011],[2012] 
from vw_pivot_sales

-- Now the problem with the above is the initial SELECT * and really you should select specific columns and maybe handle NULL values
-- to show you what I mean, lets play with that VIEW...
alter view vw_pivot_sales as
SELECT Customername,
       SalespersonN,
       isnull([2010],0) as [2010],
       isnull([2011],0) as [2011],
       isnull([2012],0) as [2012],
       isnull([2013],0) as [2013],
       isnull([2014],0) as [2014],
       isnull([2015],0) as [2015],
       isnull([2016],0) as [2016],
       isnull([2017],0) as [2017],
       isnull([2018],0) as [2018],
       isnull([2019],0) as [2019],
       isnull([2020],0) as [2020]
FROM  (SELECT Cu.Customername, Cu.SalespersonN, ART.salesanalysis, year(ART.postingdate) as yr
       FROM   ARTransaction AS ART
       JOIN   Customer AS Cu ON ART.CustomerN = Cu.CustomerN
       WHERE  YEAR(ART.PostingDate) >= '2010'
--     AND    YEAR(ART.PostingDate) <= '2012'
       AND    Cu.SalespersonN <> 0 ) src
PIVOT
      (sum(salesanalysis) for yr in ([2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020])) pvt
GO

-- now selecting from the view avoids NULL and shows (for the user) a more accurate 0.00
select customername, salespersonn, [2010],[2011],[2012] 
from vw_pivot_sales

Open in new window


I have written a couple of Articles about PIVOT :
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html

And a mate has written one describing what dtodd was saying using generic columns :
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_654-Pivot-Rolling-Periods-Without-Using-Dynamic-T-SQL.html
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 200 total points
Comment Utility
SELECT
      Cu.CustomerName, /*Cu.SalespersonN, --optional*/
      SUM(CASE WHEN PostingDate >= '20100101' AND PostingDate < '20110101' THEN ART.SalesAnalysis ELSE 0 END) AS [2010 Sales],
      SUM(CASE WHEN PostingDate >= '20110101' AND PostingDate < '20120101' THEN ART.SalesAnalysis ELSE 0 END) AS [2011 Sales],
      SUM(CASE WHEN PostingDate >= '20120101' AND PostingDate < '20130101' THEN ART.SalesAnalysis ELSE 0 END) AS [2012 Sales]
      FROM ARTransaction AS ART
      JOIN Customer AS Cu
      ON ART.CustomerN = Cu.CustomerN
      WHERE PostingDate >= '20100101' AND
            PostingDate < '20130101' --years 2010-2012
            AND Cu.SalespersonN <> 0
                 
GROUP BY Cu.CustomerName /*, Cu.SalespersonN*/
ORDER By Cu.CustomerName /*, Cu.SalespersonN*/


Btw, never perform a function on a column in a WHERE clause unless it is truly required.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now