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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.