Simple count items by month - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Simple count items by month

Author

Topic

saltcod
Starting Member

13 Posts

Posted - 2010-01-13 : 09:44:23

Hi All - just discovered this forum - looking forward to participating.

I'm trying to a SIMPLE monthly grouping of sales.

I'm looking to turn this:

Smith Inc. $50 Jan 2009
Smith Inc. $500 Jan 2009
Smith Inc. $200 Jan 2009
Jones Inc. $200 Jan 2009
Clark Co. $100 Feb 2009
Clark Co. $200 Feb 2009

Into this:

Company Sales Month
-------- ----- -----
Smith Inc. 3 Jan 2009
Jones Inc. 1 Jan 2009
Clark Co. 2 Feb 2009

Its a very simple COUNT and GROUP BY but I can't get it to work.

SELECT Company, COUNT(Sales) AS Sales, FROM mytable GROUP BY Company, Month

Thanks for any help resolving this seemingly easy issue.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 09:50:36

Can you give the table structure fitting to the example data?
We need to know how the 'Jan 2009' is stored.

Edit: typo

No, you're never too old to Yak'n'Roll if you're too young to die.

saltcod
Starting Member

13 Posts

Posted - 2010-01-13 : 09:52:35

Thanks, sorry about that - I tried to oversimplify.

The date format is actually DD/MM/YYYY .... So I want to pull out the Month to aggregate the totals.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 09:58:34

Hm...
So is it a varchar with something like this '13/01/2010'?
Or is it a column with data type datetime?

No, you're never too old to Yak'n'Roll if you're too young to die.

saltcod
Starting Member

13 Posts

Posted - 2010-01-13 : 10:09:36

yeah, datetime format

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 10:20:26

Try this:
SELECT
Company,
COUNT(*) AS Sales,
month(your_datecol) as [Month],
year(your_datecol) as [Year]
FROM mytable
GROUP BY
Company,
year(your_datecol),
month(your_datecol)

No, you're never too old to Yak'n'Roll if you're too young to die.

saltcod
Starting Member

13 Posts

Posted - 2010-01-13 : 10:22:02

I'm getting close, but it doesn't group by company properly.

This:

SELECT Company, Month(PurchaseDate) AS Month,COUNT(Company) AS Sales
FROM salestable
GROUP BY Company,Month(PurchaseDate);

Leaves me with repeats unless the sales were on the same DAY. IE:

Company Sales Month
------- ----- -----
Jones Co. 2 28/02/2009
Jones Co. 1 29/02/2009

So it is grouping, but looks to be doing so by EXACT date.

saltcod
Starting Member

13 Posts

Posted - 2010-01-13 : 10:24:58

oops - webfred: we posted at the same time -- I'll look at your code and post back! Thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 10:26:34

quote:
Originally posted by saltcod

I'm getting close, but it doesn't group by company properly.

This:

SELECT Company, Month(PurchaseDate) AS Month,COUNT(Company) AS Sales
FROM salestable
GROUP BY Company,Month(PurchaseDate);

Leaves me with repeats unless the sales were on the same DAY. IE:

Company Sales Month
------- ----- -----
Jones Co. 2 28/02/2009
Jones Co. 1 29/02/2009

So it is grouping, but looks to be doing so by EXACT date.

Month(PurchaseDate) cannot give you results like '28/02/2009'. Can you show the actual result of the query?

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 10:31:35

If you want to group by "date", rather than Month / Year columns, you might like to "round" the dates to 1st of the month, e.g.:


SELECT Company,
       DATEADD(Month, DATEDIFF(Month, 0, your_datecol), 0) AS MyDate,
       COUNT(*) AS Sales
FROM mytable
GROUP BY Company, DATEADD(Month, DATEDIFF(Month, 0, your_datecol), 0)
ORDER BY Company, MyDate

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 10:32:06

quote:
Originally posted by saltcod

oops - webfred: we posted at the same time -- I'll look at your code and post back! Thanks!

Good luck

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 10:32:11

quote:
Originally posted by saltcod

I'm getting close, but it doesn't group by company properly.

This:

SELECT Company, Month(PurchaseDate) AS Month,COUNT(Company) AS Sales
FROM salestable
GROUP BY Company,Month(PurchaseDate);

Leaves me with repeats unless the sales were on the same DAY. IE:

Company Sales Month
------- ----- -----
Jones Co. 2 28/02/2009
Jones Co. 1 29/02/2009

So it is grouping, but looks to be doing so by EXACT date.

Nope thats not true. you're taking month() so you wont get above output. the output will be


Company		Sales		Month
-------		-----		-----
Jones Co. 	3 		2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 10:33:18

Wait until he has tried my solution...

No, you're never too old to Yak'n'Roll if you're too young to die.

saltcod
Starting Member

13 Posts

Posted - 2010-01-13 : 10:35:37

Excellent.

Webfred's last query gives:


Company	 	Sales	Month	Year
--------	-----	-----	----
Smith Inc. 	3	1	2009
Jones Inc. 	1	1 	2009
Clark co. 	2	2	2009
Clark Co.	2	2 	2009

Perfect. Now I just need to get the date fields together so it looks like:


Company	 	Sales	Date
--------	-----	-----	 
Smith Inc. 	  3	Jan 2009

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 10:40:11

quote:
Originally posted by saltcod

Excellent.

Webfred's last query gives:

Company Sales Month Year
-------- ----- ----- ----
Smith Inc. 3 1 2009
Jones Inc. 1 1 2009
Clark co. 2 2 2009
Clark Co. 2 2 2009

Perfect. Now I just need to get the date fields together so it looks like:

Company Sales Date
-------- ----- -----
Smith Inc. 3 Jan 2009

use like


SELECT Company,
      DATEADD(Month, DATEDIFF(Month, 0, your_datecol), 0),
       COUNT(*) AS Sales
FROM mytable
GROUP BY Company, DATEADD(Month, DATEDIFF(Month, 0, your_datecol), 0)
ORDER BY Company, DATEADD(Month, DATEDIFF(Month, 0, your_datecol), 0)

and use formatting functions in front end to get your format

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 10:49:33

Alternately, you could try this too..

select company,count(*) as sales,right(convert(varchar(11),PurchaseDate,113),8)
from <urtable> 
group by company,right(convert(varchar(11),PurchaseDate,113),8)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 10:51:59

quote:
Originally posted by vijayisonly

Alternately, you could try this too..
select company,count(*) as sales,right(convert(varchar(11),PurchaseDate,113),8)
from <urtable>
group by company,right(convert(varchar(11),PurchaseDate,113),8)

That looks not bad but what is about order by?

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 10:52:00

Oh Blast, I put DAY rather than MONTH in mine. I'll change it retrospectively to Snipe you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 10:52:59

quote:
Originally posted by Kristen

Oh Blast, I put DAY rather than MONTH in mine. I'll change it retrospectively to Snipe you

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 10:55:52

"right(convert(varchar(11),PurchaseDate,113),8)"

My recommendation would be to keep it as a DateTime datatype as long as possible.

SQL is much slower converting dates to Strings than it is doing the (somewhat ghastly to read) date rounding:

DATEADD(Month, DATEDIFF(Month, 0, your_datecol), 0)

saltcod
Starting Member

13 Posts

Posted - 2010-01-13 : 13:25:43

Hey guys - sorry for the late reply --I had a long lunch =)

DATEADD worked like a charm.

This has been great help -- thanks.

Terry

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources