getting a count using datename... 1

[gtjr92]

I am trying to get a count of entries per month. The date created field is the field that holds the date of an entry.
When i run the below query it only gives me data from one day for each month, it does not iterate through the whole month. How can i have it so it will give me the total count from each month.

Select  
 distinct datename(Month,DateCreated)As Month,
 datepart(Month,DateCreated)As Monthid,
 datepart(year,DateCreated) As Year, 
 count(*) As EntryCount 
   from blog_entry  
    WHERE blog_Entry.IsDeleted = 0 and 
  datename(Month,DateCreated)=datename(Month,DateCreated)
group by datecreated
order by monthid,year

 

[r937]

the first part that's weird is

datename(Month,DateCreated)=datename(Month,DateCreated)

that's always gonna be true, don't you think?

perhaps you meant this --

datename(Month,DateCreated)=datename(Month,getdate())

the next part that's not quite right is this --

group by datecreated

this will give you one row in the result for each value of datecreated


r937.com | rudy.ca

 

[gtjr92]

i changed the datename(Month,DateCreated)=datename(Month,getdate())
like you suggested. if i run the query with that and keep the datecreated group clause i do not get any rows returned. IF i remove the group by datecreated i get
Column DateCreated is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[r937]

hint: in the GROUP BY, put the columns that you want a count of entries for -- probably year and month



r937.com | rudy.ca

 

[gtjr92]

tried it still get no rows.
tried this

group by datename(Month,DateCreated),datepart(Month,DateCreated),datepart(year,DateCreated),datecreated

also tried adding just the datepart month and date created fields in the group by same thing no rows returned.

 

[vongrunt]

No rows = WHERE clause is f**ed up.

There are some other weirdos in this query... later.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[gtjr92]

There are some other weirdos in this query... later. "
well then how about helping me out instead of making snide remarks!

 

[r937]

if datecreated is in your GROUP BY, then you are going to get one row in the result for every different day

please show your entire query

r937.com | rudy.ca

 

[gtjr92]

my entire query was in my first post, but here it is with the changes i made after reading the previous posts

	Select  distinct datename(Month,DateCreated)As Month,datepart(Month,DateCreated)As Monthid,
datepart(year,DateCreated) As Year, count(*) As EntryCount 
 from blog_entry  
WHERE blog_Entry.IsDeleted = 0 and datename(Month,DateCreated)=datename(Month,getdate())
group by datename(Month,DateCreated),datepart(Month,DateCreated),datepart(year,DateCreated),datecreated
order by monthid,year

if i just group by date created, or any of the datepart/datename i get no rows returned. when i do my original query i only get results back for one day of each month instead of a total of all days for each months

original query

Select  
 distinct datename(Month,DateCreated)As Month,
 datepart(Month,DateCreated)As Monthid,
 datepart(year,DateCreated) As Year, 
 count(*) As EntryCount 
   from blog_entry  
    WHERE blog_Entry.IsDeleted = 0 and 
  datename(Month,DateCreated)=datename(Month,DateCreated)
group by datecreated
order by monthid,year

 

[r937]

try this --

select datename(Month,DateCreated) as Month
     , datepart(Month,DateCreated) as Monthid
     , datepart(Year,DateCreated)  as Year
     , count(*)       as EntryCount 
  from blog_entry  
 where blog_entry.IsDeleted = 0 
   and datename(Month,DateCreated)
     = datename(Month,getdate())
group 
    by datename(Month,DateCreated) 
     , datepart(Month,DateCreated) 
     , datepart(Year,DateCreated) 
order 
    by Monthid
     , Year

r937.com | rudy.ca

 

[SQLDenis]

what about this?

Select  
 distinct datename(Month,DateCreated)As Month,
 datepart(Month,DateCreated)As Monthid,
 datepart(year,DateCreated) As Year, 
 count(*) As EntryCount 
   from blog_entry  
    WHERE blog_Entry.IsDeleted = 0 
group by datename(Month,DateCreated),datepart(year,DateCreated)
order by monthid,year

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Oops disregard my post I thought you wanted all months, not all days for a month

Use rudy's query, only you probably want to make it sargable instead

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Yup, that and... better swap monthid and [year] in ORDER BY clause - in case data spans across many years.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[r937]

Denis, that's hardly fair -- you could at least include a link to a web site somewhere where they do a credible job of defining sargable

(i have one or two such links, but i can't find them at the moment -- maybe you have one?)

also, DISTINCT is not required (in this example) because of the GROUP BY

r937.com | rudy.ca

 

[vongrunt]

Blah... forgetit , query already selects only data from May only (all years?).

About sarg thing... here.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[r937]

thanks, vongrunt, that's one of the links i had

key point: "expressions that include a function on a column... are not sargable"


r937.com | rudy.ca

 

[SQLDenis]

so change
and datename(Month,DateCreated) = datename(Month,getdate())

To

and DateCreated >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+0, 0)
and DateCreated < DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0)

Here are some ways to make your queries sargable (

http://sqlservercode.blogspot.com/2005/12/sql-query-optimizations.html)

yes indeed selfish selfpromotion ;-)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Star for vongrunt for the link

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gtjr92]

I already tried rudy's query as i stated in my 2nd post
"if i run the query with that and keep the datecreated group clause i do not get any rows returned"

other ideas?
i appreciate all the help..

 

[gtjr92]

tried sqldenis too still i get no rows