T-sql group on date and year field

I am trying to group on a date (year and month ) field but I am not geting expected values.

Eaxample

table has
date field               value
-----------------------------------
january 2009           1        
january 2009            2
feduary 2009           1
feduary 2009            2
march   2009            1
march  2009             2
''
january 2010            1
january 2010             2
feduary 2010            1
Feduary 2010            2
march   2010             1
march    2010             2


January 2011              1
January 2011               2
feduary 2011                1
feduary 2011                2
march   2011                 2
march   2011                 2

SELECT  
    DATENAME(mm, date_field) AS Month,  
    DATENAME(yyyy, date_field) AS Year,  
    COUNT(*) AS Total  
FROM Articles AS article  
GROUP BY  
    DATENAME(mm, date_filed),  
    DATENAME(yyyy, date_file)  


I am expecting this output . How can I get this output?

Date_field                   Total
--------------------------------------------
january 2009                 2
febuary 2009                 2
march    2009                 2
january  2010                 2
febuary 2010                   2
march    2010                 2
january   2011                2
febuary  2011                2
march     2011               2
TClevelAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
This seems odd.

Author stated "Scott it works", asked me several follow up qs -- which I answered -- and then says that's all worth zero?
0
 
tim_csCommented:
What output are you getting from that query?
0
 
TClevelAuthor Commented:
I just found this query. Should this give me what I am looking for?

I used this one earlier

SELECT  
    DATENAME(MONTH, date_field) AS Month,  
    DATENAME(YEAR, date_field) AS Year,  
    COUNT(*) AS Total  
FROM Articles AS article  
GROUP BY  
    DATENAME(MONTH, date_filed),  
    DATENAME(YEAR, date_file)  

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LowfatspreadCommented:
are you getting output or an error message?


the code you are showing us has an inconsistent name for the date_field...


SELECT  
    DATENAME(MONTH, date_field) AS Month,  
    DATENAME(YEAR, date_field) AS Year,  
    COUNT(*) AS Total  
FROM Articles AS article  
GROUP BY  
    DATENAME(MONTH, date_filed),   filed not field
    DATENAME(YEAR, date_file)          file not field


it doesn't matter if you use MONTH or MM or M etc ....

ps you wont get consistent output unless you include an order by clause...


Is this coursework/homework?
0
 
TClevelAuthor Commented:
I am using this as an example of what I am dioing at work. the fields I am using here is not the fields I am using at work.  At work I am getting output but not what I am expecting.

SELECT  
    DATENAME(MONTH, date_field) AS Month,  
    DATENAME(YEAR, date_field) AS Year,  
    COUNT(*) AS Total  
FROM Articles AS article  
GROUP BY  
    DATENAME(MONTH, date_field),  
    DATENAME(YEAR, date_field)          

0
 
LowfatspreadCommented:
this is how you should specify the statement

select datename(month,thedate) as [Month]
     ,datename(year,thedate) as [year]
    ,[total]
from (select convert(char(6),datefield,112)+'01' as thedate,count(*)
   from yourtable
  group by convert(char(6),datefield,112)+'01'
 ) as x
order by Thedate

please explain what output you actually get and how that differs from what you expect / want to achieve.

0
 
TClevelAuthor Commented:
it should look like this when output

Date_field                   Total
--------------------------------------------
january 2009                 2
febuary 2009                 2
march    2009                 2
january  2010                 2
febuary 2010                   2
march    2010                 2
january   2011                2
febuary  2011                2
march     2011               2
0
 
LCSandman8301Commented:
does your table contain the date? or does it contain the date as above?
if the data has a text date_field you should just be able to group by the datefield column.
select date_field as date_field,
 count(*) as [total]
from yourtable
group by date_field

Open in new window

0
 
TClevelAuthor Commented:
here is what that field look like in the table. it is a int in the database table.
200901
200902

201001
201002
I need it to look like this January 2009  ect...
0
 
LCSandman8301Commented:
try this
select date_field/100 as year, datename(mm,date_field%100) as month, count(*)
from yourtable
group by date_field/100, datename(mm,date_field%100)

Open in new window

0
 
Scott PletcherSenior DBACommented:

SELECT
    DATENAME(MONTH, CAST(art_group.date_field * 100 + 1 AS varchar(8))) + ' ' +
        CAST(art_group.date_field / 100 AS varchar(4)) AS  [Date Field],
    art_group.Total
FROM (
    SELECT
        date_field, 
        COUNT(*) AS Total  
    FROM dbo.Articles AS article
    GROUP BY
        date_field
) AS art_group
ORDER BY
    art_group.date_field

Open in new window

0
 
LowfatspreadCommented:
varchar(9)  in scotts  (for september)
0
 
Scott PletcherSenior DBACommented:
D'OH, good point!
0
 
Scott PletcherSenior DBACommented:
OOPS, wait a minute.

My CAST is NOT for the final month result,  but for converting the date_field to char, so EIGHT is right.

It is less overhead to convert the date *after* GROUPing it, so that's what I did.
0
 
LCSandman8301Commented:
why are you multiplying by 100? shouldn't you be doing a modulo (%) and then your cast can be turned to a char(2). but that shouldn't matter either because the datename function takes an int for the second parameter just fine.
0
 
TClevelAuthor Commented:
Scott it works but I need to make Total said  Grand Total
how can I do this?

Year            Grand Total
---------------------------------
20009           2222222222
20009           3333333333
ect........

SELECT
    DATENAME(MONTH, CAST(art_group.date_field * 100 + 1 AS varchar(8))) + ' ' +
        CAST(art_group.date_field / 100 AS varchar(4)) AS  [Date Field],
    art_group.Total ----------------
FROM (
    SELECT
        date_field,
        COUNT(*) AS Total  ------------------
    FROM dbo.Articles AS article
    GROUP BY
        date_field
) AS art_group
ORDER BY
    art_group.date_field

0
 
LCSandman8301Commented:
art_group.Total as [Grand Total]
0
 
Scott PletcherSenior DBACommented:
SELECT
    DATENAME(MONTH, CAST(art_group.date_field * 100 + 1 AS varchar(8))) + ' ' +
        CAST(art_group.date_field / 100 AS varchar(4)) AS  [Date Field],
    art_group.Total AS [Grand Total]
FROM ...
0
 
Scott PletcherSenior DBACommented:
[Plus by converting the date after the grouping, I can sort in calendar date sequence but showing the month name.]
0
 
Scott PletcherSenior DBACommented:
>> why are you multiplying by 100? <<

Because the column contains only yyyymm.  To make it a valid date format, I need to add the day, so 100 * yyyymm = yyyyymm00, then add 1 to get yyyymmdd, where dd = 01 :-) .


>> but that shouldn't matter either because the datename function takes an int for the second parameter just fine. <<

Yes, but it interprets an int value there as days elapsed in 01 Jan 1900.  The value I was passing in was not the number of days since the base date, but itself a date.

SQL will *ALWAYS* correctly interpret a *character* string in format 'yyyymmdd', no matter what the language, date settings, etc., are.
0
 
LowfatspreadCommented:
@scott

tired eyes sorry

;-)
0
 
TClevelAuthor Commented:
One last thing how can I format Total  
example  if results is   2234524  how can I format it with commas like this   2,234,525

 SELECT date_field,   COUNT(*) AS Total  
 FROM dbo.Articles AS article
0
 
Scott PletcherSenior DBACommented:
Grrr, that's harder than it seems like it should be.

The purist answer is to do the formatting in the front-end that displays the value.

If you need to do it in SQL, you can use a user-defined function or CONVERT with a CAST to money and using text functions to get what you want, like so:

REPLACE(CONVERT(varchar(20), CAST(total AS money), 1), '.00', '')
|
REPLACE(CONVERT(varchar(20), CAST(COUNT(*) AS money), 1), '.00', '')
0
 
TClevelAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for TClevel's comment http:/Q_27496413.html#37298405

for the following reason:

I expect this solustiom because it help me understand the process of what I was trying to do.
0
 
TClevelAuthor Commented:
I apologize I lost track after going way for the holiday. I appreciate you for solving my issue. Everything works. Thank you again for all your work.
0
 
TClevelAuthor Commented:
Scott did you get the points?
0
 
Scott PletcherSenior DBACommented:
Yes.

Thank you!

Sorry for the mix-up.  I was just a bit confused :-) .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.