Get Total and Avg Values

Question

Get Total and Avg Values

David-155102

SSCarpal Tunnel

Points: 4361
More actions
October 19, 2009 at 4:23 am

#72543

I have a test table as follows:
create table #test
(item varchar(10),
period int,
fYear int,
total_value int,
total_qty int
)
insert into #test
select 'A',1,2009,100,10
union all
select 'A',2,2009,50,10
union all
select 'A',3,2009,80,20
union all
select 'A',4,2009,10,1
union all
select 'A',5,2009,200,50
union all
select 'A',6,2009,150,12
select * from #test
drop table #test
What I need to do is the following:
1. Get the total value and total qty for each item for a 12 month period (I have only added 6 months to my example). The query would take a year parameter to determine which values to include
2. The query would also take a set of period/year combinations to return the average value and qty for each item that was sold
3. If the item was sold during the 12 month period but NOT during any of the period/year combinations then it must still be listed but with NULL as the average value and qty
I have already worked out how to create a sort of array for the period/year combinations. Its just trying to get 1 and 3 that is causing me problems.
Using my example, the total value would be 590 and the total qty would be 103, if the period/year combinations were 1 2009, 2 2009 and 3 2009 then the avg value would be 5.75 and the average qty would be 13.3. If the item was not sold during these period/year combinations I would see the 590 for total value, 103 for total qty, NULL for avg value and NULL for avg qty.
Any ideas?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Thanks for posting a test structure. I still have a question though, what is the format of the data you wish to return?

Do you want it just grouped by item and year? Or should it be grouped by item, year, and period with rolling totals and averages and a grand total at the end?

You may also want to include in your test data rows that will meet all your requirements, otherwise you will not get a fully tested solution.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 2

Hi Jack,

The output only needs to be Item, Total Value, Total Qty, Avg Value and Avg Qty, the last two only having values if they were sold during the Period/Year combinations.

Thanks

David

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

It's probably me, but I still don't get what you need. You are not including period in your output so your grouping would be on Item and Year so your averages would be based on the item and year so in your example this query:

select

item,

fYear,

SUM(total_value) AS total_value,

SUM(total_qty) AS total_quantity,

AVG(CONVERT(DECIMAL(10,2), total_value)) AS avg_value,

AVG(CONVERT(DECIMAL(10,2), total_qty)) AS avg_quantity

from

#test

GROUP BY

item,

fyear

Returns this result:

itemfYeartotal_valuetotal_quantityavg_valueavg_quantity

A2009590 103 98.33333317.166666

So how would you want your averages calculated?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 4

Yes, it is a bit unclear I must admit! Ok, I'll try and explain, so in the first instance I needed to work out what the average selling price and average quatities sold were for a particular group of Periods/Years. So if the user selected Jan 2009, Feb 2009 and Mar 2009 I needed to work out what was sold during those months and then work out the averages for each item based upon that data.

What I then did with that data was to show a comparison of how those averages reflected the previous 12 months sales of those same items. However, this only returned data for anything sold in the period/years defined in the criteria but what my user wanted to see was the total sales value and qty for anything sold in the previous 12 months regardless of whether it was also sold during the period/year criteria. The period/year criteria may not fall within the previous 12 months, so in my example the user has selected Jan 09, Feb 09 and Mar 09 on which to base the averages but the previous 12 months would be Jan 08 to Dec 09.

I fear that this explanation does not make it any clearer but here goes!!

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 5

I've had a bit of a play and have simplified what I am asking for help on, first the sample code and data:

use tempdb

go

--Create table to hold this years sales

CREATE TABLE #CurrentYr

(

Customer VARCHAR(10),

CustomerType VARCHAR(10),

Product VARCHAR(10),

ProductCode VARCHAR(10),

Total FLOAT

)

--Create table to hold last years sales

CREATE TABLE #LastYr

(

Customer VARCHAR(10),

CustomerType VARCHAR(10),

Product VARCHAR(10),

ProductCode VARCHAR(10),

Total FLOAT

)

--Insert current year data

INSERT INTO #CurrentYr

SELECT 'Jim','S','Radio','R1',10

UNION ALL

SELECT 'Jim','S','TV','T1',20

UNION ALL

SELECT 'Jim','S','Microwave','M1',30

UNION ALL

SELECT 'Tracy','S','Radio','R1',20

UNION ALL

SELECT 'Howard','S','TV','T1',10

UNION ALL

SELECT 'Frank','S','Microwave','M1',10

--Insert last year data

INSERT INTO #LastYr

SELECT 'Jim','S','Radio','R1',20

UNION ALL

SELECT 'Jim','S','TV','T1',10

UNION ALL

SELECT 'Jim','S','Car','C1',3000

UNION ALL

SELECT 'Tracy','S','Microwave','M1',20

SELECT Customer, SUM(Total) AS CurrentYear

FROM #CurrentYr

GROUP BY Customer

SELECT Customer, SUM(Total) AS LastYear

FROM #LastYr

GROUP BY Customer

DROP TABLE #CurrentYr

DROP TABLE #LastYr

So what I need is for the two tables to be combined to show each customer's current year and previous year's spend on the same line, it sounds simple but I just can't get it to do what I want without duplicating data!

Thanks

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

David-155102 (11/2/2009)
I've had a bit of a play and have simplified what I am asking for help on, first the sample code and data:
use tempdb
go
--Create table to hold this years sales
CREATE TABLE #CurrentYr
(
Customer VARCHAR(10),
CustomerType VARCHAR(10),
Product VARCHAR(10),
ProductCode VARCHAR(10),
Total FLOAT
)
--Create table to hold last years sales
CREATE TABLE #LastYr
(
Customer VARCHAR(10),
CustomerType VARCHAR(10),
Product VARCHAR(10),
ProductCode VARCHAR(10),
Total FLOAT
)
--Insert current year data
INSERT INTO #CurrentYr
SELECT 'Jim','S','Radio','R1',10
UNION ALL
SELECT 'Jim','S','TV','T1',20
UNION ALL
SELECT 'Jim','S','Microwave','M1',30
UNION ALL
SELECT 'Tracy','S','Radio','R1',20
UNION ALL
SELECT 'Howard','S','TV','T1',10
UNION ALL
SELECT 'Frank','S','Microwave','M1',10
--Insert last year data
INSERT INTO #LastYr
SELECT 'Jim','S','Radio','R1',20
UNION ALL
SELECT 'Jim','S','TV','T1',10
UNION ALL
SELECT 'Jim','S','Car','C1',3000
UNION ALL
SELECT 'Tracy','S','Microwave','M1',20
SELECT Customer, SUM(Total) AS CurrentYear
FROM #CurrentYr
GROUP BY Customer
SELECT Customer, SUM(Total) AS LastYear
FROM #LastYr
GROUP BY Customer
DROP TABLE #CurrentYr
DROP TABLE #LastYr
So what I need is for the two tables to be combined to show each customer's current year and previous year's spend on the same line, it sounds simple but I just can't get it to do what I want without duplicating data!
Thanks

Is this what you're after

WITH CurrentYear AS (

SELECT Customer, SUM(Total) AS CurrentYear

FROM #CurrentYr

GROUP BY Customer),

LastYear AS (

SELECT Customer, SUM(Total) AS LastYear

FROM #LastYr

GROUP BY Customer)

SELECT COALESCE(a.Customer,b.Customer) AS Customer,

a.CurrentYear,

b.LastYear

FROM CurrentYear a

FULL OUTER JOIN LastYear b ON a.Customer=b.Customer

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 7

Have you got a SQL2K version of that please? This particular server is still on the old version so it doesn't like the CTE.

Thanks

David

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 8

SELECT COALESCE(a.Customer,b.Customer) AS Customer,

a.CurrentYear,

b.LastYear

FROM (

SELECT Customer, SUM(Total) AS CurrentYear

FROM #CurrentYr

GROUP BY Customer) a

FULL OUTER JOIN (

SELECT Customer, SUM(Total) AS LastYear

FROM #LastYr

GROUP BY Customer) b ON a.Customer=b.Customer

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 9

David-155102

SSCarpal Tunnel

Points: 4361

November 2, 2009 at 10:42 am

#1073907

Cheers Mark 🙂