x

Totals across a rows columns and potentialy multiple rows

Hi, I thought I posted this question last week but cannot find trace so apologies if I duplicate it:

I have a requirement to total across a row but only columns in a user specified range. My results may require a grand total of multiple rows depending on the user parameters.
The actual spec is to calculate total sales over a given period range for a specified customer range from a table that hold sales by year/period. The unique key on the table is customer/year and for each row there are 13 period columns: period01....period13.

The user wishes to run the report for example with the following criteria: Customer A ~ Customer A, Year/Period: 0810 ~ 0906

Therefore not only do I need only total across the row for year 08 for period 10 to 12 only but then add to it the total of period columns 1-6 from the row year 09 Eg dataset:

Customer Year  Period01 Period02 ..03 ..04 ..05 ..06 ..07 ..08 ..09 ..10 ..11 ..12 ..13
CUSTA    2008     5     4         1    2    3    2    1     1    1   1     1   1    2 
CUSTA    2009     2     3         2    1    2    5    6     7    2   0     4   1    5

Result output = 20

I’m not much kop at in depth sql; a few static columns totalled across the row is my level; so I’m hoping some nice kind expert out there is able & willing to point me in the right direction. I will probably need apply any sql to a crystal report which I think is the end users main aim and from where the user parameters will be parsed from.

Just seen the text formatting of the resultant question, sorry bout that but I'm sure you get the jist of my example data.

Many thanks in advance 

more ▼

asked Mar 15, 2010 at 07:29 AM in Default

Richard 1 gravatar image

Richard 1
13 1 1 1

Instead of 13 individual SELECTs being UNIONed together, there's a simple way to do this with a CROSS TAB but only if the data is properly normalized. You can have end of row totals, section subtotals, and grand totals by customer, year, etc, et.

So, let me ask... please post the CREATE TABLE statement along with some INSERT/SELECT's to populate the table and we'll give it a whirl. Or, you can use 13 SELECTs. ;-)
May 23, 2010 at 11:45 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

On SQL Server 2005 and above you can use UNPIVOT operator to rotate the columns into the rows and then you can simply select and sumarize the data.

Here you have a complete solution.

DECLARE @tbl TABLE(
    Customer varchar(20),
    [Year] int,
    Period1 int,
    Period2 int,
    Period3 int,
    Period4 int,
    Period5 int,
    Period6 int,
    Period7 int,
    Period8 int,
    Period9 int,
    Period10 int,
    Period11 int,
    Period12 int,
    Period13 int
)

INSERT INTO @tbl(
    Customer,
    [Year],
    Period1,
    Period2,
    Period3,
    Period4,
    Period5,
    Period6,
    Period7,
    Period8,
    Period9,
    Period10,
    Period11,
    Period12,
    Period13
)
SELECT 'CUSTA', 2008, 5, 4, 1, 2, 3, 2, 1, 1, 1, 1, 1, 1, 2 UNION ALL
SELECT 'CUSTA', 2009, 2, 3, 2, 1, 2, 5, 6, 7, 2, 0, 4, 1, 5


;WITH DataTable AS (
    SELECT 
        Customer, 
        [Year], 
        Period, 
        Data
    FROM 
    (SELECT
        Customer,
        [Year],
        Period1 AS "1",
        Period2 AS "2",
        Period3 AS "3",
        Period4 AS "4",
        Period5 AS "5",
        Period6 AS "6",
        Period7 AS "7",
        Period8 AS "8",
        Period9 AS "9",
        Period10 AS "10",
        Period11 AS "11",
        Period12 AS "12",
        Period13 AS "13"
    FROM @tbl
    ) p
    UNPIVOT
        (Data FOR Period in ("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13")
    ) AS unpvt
)
SELECT 
    SUM(Data) 
FROM DataTable
WHERE Customer = 'CUSTA' AND ([Year] * 100 + Period) >= 200810 AND ([Year] * 100 + Period) <= 200906

For SQL 2000 you can achieve result using several UNIONS. Here you have the SQL 2000 solution

DECLARE @tbl TABLE(  Customer varchar(20),  [Year] int,  Period1 int,  Period2 int,  Period3 int,  Period4 int,  Period5 int,  Period6 int,  Period7 int,  Period8 int,  Period9 int,  Period10 int,  Period11 int,  Period12 int,  Period13 int )

INSERT INTO @tbl( Customer, [Year], Period1, Period2, Period3, Period4, Period5, Period6, Period7, Period8, Period9, Period10, Period11, Period12, Period13 ) SELECT 'CUSTA', 2008, 5, 4, 1, 2, 3, 2, 1, 1, 1, 1, 1, 1, 2 UNION ALL SELECT 'CUSTA', 2009, 2, 3, 2, 1, 2, 5, 6, 7, 2, 0, 4, 1, 5

SELECT Sum(Data) FROM
(SELECT Customer, [Year], 1 AS Period, Period1 Data FROM @tbl UNION ALL SELECT Customer, [Year], 2 AS Period, Period2 Data FROM @tbl UNION ALL SELECT Customer, [Year], 3 AS Period, Period3 Data FROM @tbl UNION ALL SELECT Customer, [Year], 4 AS Period, Period4 Data FROM @tbl UNION ALL SELECT Customer, [Year], 5 AS Period, Period5 Data FROM @tbl UNION ALL SELECT Customer, [Year], 6 AS Period, Period6 Data FROM @tbl UNION ALL SELECT Customer, [Year], 7 AS Period, Period7 Data FROM @tbl UNION ALL SELECT Customer, [Year], 8 AS Period, Period8 Data FROM @tbl UNION ALL SELECT Customer, [Year], 9 AS Period, Period9 Data FROM @tbl UNION ALL SELECT Customer, [Year], 10 AS Period, Period10 Data FROM @tbl UNION ALL SELECT Customer, [Year], 11 AS Period, Period11 Data FROM @tbl UNION ALL SELECT Customer, [Year], 12 AS Period, Period12 Data FROM @tbl UNION ALL SELECT Customer, [Year], 13 AS Period, Period13 Data FROM @tbl ) DataTable WHERE Customer = 'CUSTA' AND ([Year] * 100 + Period) >= 200810 AND ([Year] * 100 + Period) <= 200906
more ▼

answered Mar 15, 2010 at 09:13 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Hi, many thanks Pavel, yes that works well on our sql2005 server but it's my own fault I didn't specify its our primary sql2000 server I have the issue with. Would have helped you wouldn't it. Can you assist? Thanks again for what was a super quick turnaround.
Mar 15, 2010 at 10:14 AM Richard 1
Added SQL server 2000 solution to the answer. Solution is simple by using several unions
Mar 15, 2010 at 11:09 AM Pavel Pawlowski
What a star, excellent, cheers :-)
Mar 15, 2010 at 12:04 PM Richard 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1950
x987
x476
x3

asked: Mar 15, 2010 at 07:29 AM

Seen: 2104 times

Last Updated: Mar 15, 2010 at 11:19 AM