Solved

Multiple Sum Columns per Row

Posted on 2013-01-25
4
738 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 75 total points
ID: 38821980
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
ID: 38823456
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
ID: 38823608
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:
Scott Pletcher earned 200 total points
ID: 38827441
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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