Summarizing Data into One Record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Summarizing Data into One Record

Author

Topic

jeanh
Starting Member

20 Posts

Posted - 2004-04-09 : 16:24:56

I wanted to try to take a file of discrete sales transactions (basically item number, date, and quantity sold) and create a record that would have Year-to-date, Month-to-date and Week-t-date totals. I got half-way there with the query below:

select transactionentry.itemid as itemid,
[transaction].time as ttime,
isnull((select sum(transactionentry.quantity) where datepart( year, [transaction].time ) = datepart( year, getdate() ) ), 0) YTD,
isnull((select sum(transactionentry.quantity) where datepart( month, [transaction].time ) = datepart( month, getdate() ) ), 0) MTD,
isnull((select sum(transactionentry.quantity) where datepart( week, [transaction].time ) = datepart( week, getdate() ) ), 0) WTD

FROM transactionentry
LEFT JOIN [transaction] on transactionentry.transactionnumber = [transaction].transactionnumber
LEFT JOIN item on [transactionentry].itemid = item.id

where datepart( year, getdate() ) = datepart( year, [transaction].time )
group by transactionentry.itemid,
[transaction].time,
datepart( year, [transaction].time ),
datepart( month, [transaction].time ),
datepart( week, [transaction].time )

I am getting multiple records (for each change in week or month) and need to do one more summarization to collapse the data into one record that totals YTD, MTD and WTD.

Any ideas on how to do that? I tried to group by itemid only and I get a SQL error saying that one or more of the columns are invalid in the select statement because they were not in an aggregate function or a group by statement. I don't understand exactly what that meant since they WERE in an aggegate function. Perhaps you can enlighten me.

Sincerely, Jean Holland

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-09 : 16:40:18

remove "Time" from both the GROUP BY and the SELECT clauses ...

if you wish to summarize by WTD, YTD, etc, you cannot display the date/time of each transaction.

- Jeff

jeanh
Starting Member

20 Posts

Posted - 2004-04-09 : 16:45:07

Thank you Jeff, but when I do that I get this message:
An error occurred while executing query:

Column 'transaction.Time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How do I get around that?

Full text of new query follows:

select transactionentry.itemid,
isnull((select sum(transactionentry.quantity) where datepart( year, [transaction].time ) = datepart( year, getdate() ) ), 0) YTD,
isnull((select sum(transactionentry.quantity) where datepart( month,
[transaction].time ) = datepart( month, getdate() ) ), 0) MTD,
isnull((select sum(transactionentry.quantity) where datepart( week,
[transaction].time ) = datepart( week, getdate() ) ), 0) WTD
from transactionentry
left join [transaction] on transactionentry.transactionnumber = [transaction].transactionnumber
left join item on [transactionentry].itemid = item.id
where datepart( year, getdate() ) = datepart( year, [transaction].time )
group by transactionentry.itemid

Jean Holland

jeanh
Starting Member

20 Posts

Posted - 2004-04-10 : 11:03:45

I have two tables, described below:

Transaction Table

transno (long int)
date (datetime)

So, data is like this:

1001 2-3-04
1002 4-1-04
1003 4-2-04
1004 4-9-04

Entry Table

transno (long int)
itemid (long int)
quantity (int)

1001 2340 5
1001 2001 1
1001 3002 2
1002 2001 3
1002 2340 1
1003 2001 6
1004 2001 1

I need to come up with records that look like this for today's date:

itemid Weektotal Monthtotal Yeartotal

2001 1 11 11
2340 0 1 6
3002 0 2 2

Can this be done at all?

Jean Holland

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-10 : 12:02:45

-- CREATE TABLE [transaction](
-- transactionnumber INT,
-- time DATETIME)
--
-- INSERT [transaction](transactionnumber, time)
-- SELECT 1001,'2/3/2004'
-- UNION ALL
-- SELECT 1002,'4/1/2004'
-- UNION ALL
-- SELECT 1002,'4/2/2004'
-- UNION ALL
-- SELECT 1004,'4/9/2004'
--
-- CREATE TABLE transactionentry (
-- transactionnumber INT,
-- itemid INT,
-- quantity INT)
--
-- INSERT transactionentry(
-- transactionnumber,
-- itemid,
-- quantity)
--
-- SELECT 1001,2340,5
-- UNION ALL
-- SELECT 1001,2001,1
-- UNION ALL
-- SELECT 1001,3002,2
-- UNION ALL
-- SELECT 1002,2001,3
-- UNION ALL
-- SELECT 1002,2340,1
-- UNION ALL
-- SELECT 1003,2001,6
-- UNION ALL
-- SELECT 1004,2001,1

-- itemid Weektotal Monthtotal Yeartotal
--
-- 2001 1 11 11
-- 2340 0 1 6
-- 3002 0 2 2

DECLARE @report_date DATETIME

SELECT @report_date = '02/03/04'--GETDATE()

SELECT
te.itemid,
SUM(ISNULL((CASE WHEN DATEPART(WEEK,t.time) = DATEPART(WEEK,@report_date) THEN te.quantity END),0)) AS WTD,
SUM(ISNULL((CASE WHEN DATEPART(MONTH,t.time) = DATEPART(MONTH,@report_date) THEN te.quantity END),0)) AS MTD,
SUM(ISNULL((CASE WHEN DATEPART(YEAR,t.time) = DATEPART(YEAR,@report_date) THEN te.quantity END),0)) AS YTD
FROM
transactionentry te
INNER JOIN [transaction] t ON te.transactionnumber = t.transactionnumber
GROUP BY
te.itemid

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jeanh
Starting Member

20 Posts

Posted - 2004-04-10 : 12:46:43

Thanks Derrick! That put me on the right track. I think I'll be able to finish my report today. I appreciate everyone's help. Jean

Jean Holland

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources