April 28, 2008 at 3:28 pm
Hello,
I have a table that list the day items are sold. I want a query that provides monthly totals of each item for the last 3 years.
Table:
1/1/2005 Grumpy
1/2/2005 Doc
1/2/2005 Grumpy
1/2/2005 Grumpy
1/3/2005 Sleepy
So far, my count query shows:
Jan-2005 Grumpy 1
Jan-2005 Grumpy 2
Jan-2005 Doc 1
Jan-2005 Sleepy 1
SELECT Format(r.DateShipped,"mmm-yyyy") AS [Month], i.Item, Count(i.Item) AS [Count]
FROM r INNER JOIN i ON r.ItemId = i.ItemId
GROUP BY i.Item, r.DateShipped
How do I combine the "Grumpy" counts?
April 28, 2008 at 4:08 pm
We'll need more helpful information from you on this. You've posted part of your table, where's the rest? The table you've posted has 2 columns, but the reference to it in you query clearly contains 3. Plus, what does the data look like in the second table that you're joining in? How 'bout providing sample data from both tables including all columns that your query will need as well as an example of the result set you're expecting?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 28, 2008 at 4:24 pm
Are you using SQL Server 2005?
FORMAT is not a valid SQL Server function.
April 28, 2008 at 5:06 pm
I do apologize, I am working with sensitive data and must re-create the situation with test data. I would love to use the code in the "best practices" link, but I am not yet expert in its use. My 2 tables that I used to recreate the environment are:
Table r:
IDDateShippedItemId
11/1/20051
21/1/20052
31/2/20051
41/2/20051
51/3/20053
ID is PK and Autonumber, DateShipped is DateTime, ItemId is Int
Table i:
IDItemIdItem
31Grumpy
42Doc
53Sleepy
ID is PK and autonumber, ItemId is Int, Item is varchar
Query:
SELECT Format(r.DateShipped,"mmm-yyyy") AS [Month], i.Item, Count(i.Item) AS [Count]
FROM r INNER JOIN i ON r.ItemId = i.ItemId
GROUP BY i.Item, r.DateShipped;
Result:
MonthItemCount
Jan-2005Doc1
Jan-2005Grumpy1
Jan-2005Grumpy2
Jan-2005Sleepy1
Desired:
MonthItemCount
Jan-2005Doc1
Jan-2005Grumpy3
Jan-2005Sleepy1
I am working in Access 2007. The above tables are direct copy/paste from the program. Is there anything else I should provide?
April 28, 2008 at 5:21 pm
As an added bonus, I would like to better understand the code in the "best practices" link. How would I re-create what I posted with that code? If I knew that, I might be able to figure out the rest of it and post better next time.
April 28, 2008 at 7:38 pm
You should post Access questions on the Microsoft Access forum.
This forum is for questions about Microsoft SQL Server 2005.
April 28, 2008 at 8:15 pm
So sorry, I did not realize that the T-SQL was so different between the Server and Access versions. If one was to desire the outcome I specified above, how would one do it in SQL Server 2005?
April 29, 2008 at 1:31 pm
SET NOCOUNT ON
DECLARE @r TABLE (ID int, DateShipped datetime, ItemId int)
INSERT INTO @r
SELECT 1, '1/1/2005', 1 UNION ALL
SELECT 2, '1/1/2005', 2 UNION ALL
SELECT 3, '1/2/2005', 1 UNION ALL
SELECT 4, '1/2/2005', 1 UNION ALL
SELECT 5, '1/3/2005', 3 UNION ALL
SELECT 6, '2/1/2005', 2
DECLARE @i TABLE (ID int, ItemId int, Item varchar(15))
INSERT INTO @i
SELECT 3, 1, 'Grumpy' UNION ALL
SELECT 4, 2, 'Doc' UNION ALL
SELECT 5, 3, 'Sleepy'
SELECT MONTH(DateShipped) as 'Month', YEAR(DateShipped) as 'Year', Item
FROM @r r
INNER JOIN @i i
ON r.ItemID = i.ItemID
GROUP BY MONTH(DateShipped), YEAR(DateShipped), Item
April 29, 2008 at 1:49 pm
Thank you for your double-whammy reply. I will chew on the CREATE code before posting more t-sql questions.
Concerning your query, it does offer some solution, but doesn't seem to count the number of items per month. The result set is:
'Month''Year'Item
12005Doc
12005Grumpy
12005Sleepy
and I was hoping to count the number of times "Grumpy" was in month 1. (should be '3'). Thoughts?
April 29, 2008 at 2:12 pm
add the count() aggregation to the query
SELECT MONTH(DateShipped) as 'Month',
YEAR(DateShipped) as 'Year',
Item,
count(*) as sales
FROM @r r
INNER JOIN @i i
ON r.ItemID = i.ItemID
GROUP BY MONTH(DateShipped), YEAR(DateShipped), Item
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2008 at 2:28 pm
Ah, yes, of course, thank you. I did not expect that to work.
Am I to assume that by using MONTH(), that there is a type of layered aggregation so the COUNT() then isn't counting the 'days' but the 'months' (or, in other words, not counting the rows, but the summarized date data)? And that is why my original FORMAT() attempt did not work because it simply "rewrote" the date, and did not "summarize" it. Do I have that correct?
April 29, 2008 at 2:37 pm
Your initial (access) query failed because you changed the date around only in the select part of the query, not in the GROUP BY. you're still grouping by the full date, so it "didn't work".
You would have needed to do this instead:
SELECT Format(r.DateShipped,"mmm-yyyy") AS [Month],
i.Item, Count(i.Item) AS [Count]
FROM r INNER JOIN i ON r.ItemId = i.ItemId
GROUP BY i.Item, Format(r.DateShipped,"mmm-yyyy");
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2008 at 2:47 pm
Ah, I see now!
I also see that I need to study GROUP BY more thoroughly. Thank you all. Not only do I have the solution to my problem, I more fully understand the issue. And that is the best possible outcome :).
Thanks again,
jschroeder
April 30, 2008 at 4:12 pm
Thanks for picking this one up Matt. I think I messed with the query just before posting it becuase I had the COUNT(*) included when I was playing with it. Again, thanks.
April 30, 2008 at 9:12 pm
John Rowan (4/30/2008)
Thanks for picking this one up Matt. I think I messed with the query just before posting it becuase I had the COUNT(*) included when I was playing with it. Again, thanks.
Not an issue! That's how the community works - you get it most of the way there, and someone else catches your little oversights! It's great for that.... Lord knows I have plenty of my "mishaps" littering this place...:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply