Month Year Functions

[glewis1636]

I have a table that contains a date column. I would like to count the number of entries in the table grouped by Month/Year. Is this possible?


Thanks,

 

[SQLSister]

sample code.

select Count(*),year(newdate), month(newdate) from table2
Group by year(newdate), month(newdate)
Order by  year(newdate), month(newdate)

Questions about posting. See faq183-874

 

[glewis1636]

Thanks, but that gives me multiple entries for the same month/year...


for example
Count Month Year
.
.
.
2 11 1995
140 11 1995
159 11 1995
.
.
.


The output i need is:
Month/Yr Count
.
.
.
08/2004 108
09/2004 111
10/2004 181
.
.
.

 

[SQLBill]

Or do you want:

01/04 3
02/04 10

If so, I would use YYYY-MM

SELECT (CONVERT(VARCHAR(6), yourfield, 121) AS [myDate],
    COUNT(CONVERT(VARCHAR(6), yourfield, 121) AS [myTotal],
FROM mytable
GROUP BY (CONVERT(VARCHAR(6), yourfield, 121)
ORDER BY (CONVERT(VARCHAR(6), yourfield, 121) ASC

-SQLBill

 

[glewis1636]

Thanks, SQLBill.
I'm not a SQL Programmer, though I play one on TV... (Actually I'm a VB Programmer, and am trying to write some SQL code). So, I copied your code sample, changed 'yourfield' to my date field name, and got a syntax error near AS in line 1. What am I supposed to put in place of [myDate] and [myTotal]?

 

[glewis1636]

With a little help, I got it to work by changing your code a little bit...

SELECT CONVERT(VARCHAR(7), CallBackDate, 121) AS myDate,
    COUNT(CONVERT(VARCHAR(7), CallBackDate, 121)) AS myTotal
FROM SalesRep
WHERE CallBackDate is not null AND SalesRepId = 'TIMK'
GROUP BY CONVERT(VARCHAR(7), CallBackDate, 121)
ORDER BY CONVERT(VARCHAR(7), CallBackDate, 121) ASC

Thanks, SQLBill for pointing me in the right direction.

 

[SQLBill]

IT should have taken the square brackets....maybe that's a VB issue because SQL Server will accept them. Oh well at least you got it to work.

-SQLBill

 

[ESquared]

It was probably the extra left parenthesis

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[SQLBill]

D'oh................Thanks for pointing that out ESquared. I gotta learn to start counting my parenthesis.

Too bad SQL Server doesn't color code parenthesis like Excel does.

-SQLBill