Returning record count based on month

[vpekulas]

Hello All,

I'm trying to return the number of records I have in my DB
for each month of the last year.

This is what use so far:

SELECT Count(ID) AS C_COUNT FROM exp_tbl_order 
WHERE fldDATE >= '12/01/2003' 
  AND fldDATE <= '12/31/2003'

Is there a way to create it dynamically, whereby the query would
return 12 results, 1 result for each month ?

"Taxes are the fees we pay for civilized society" G.W.

 

[swampBoogie]

SELECT count(id),extract(month from fldDate) as gm
  from exp_tbl_order
WHERE fldDATE >= '12/01/2003'
  AND fldDATE <= '12/31/2003' 
group by extract(month from fldDate)

 

[vpekulas]

This doesn't quite work, I forgot to mention that I use MSSQL.

"Taxes are the fees we pay for civilized society" G.W.

 

[fluteplr]

replace extract(month from flddate) with month(flddate)

You would be better off posting questions related specifically to MSSQL in the MSSQL forum.

 

[vpekulas]

Thanks, that works. But waht I mean is to get the result for each month, so 12 records would be returned wihtout me changing the date.
The

fldDATE >= '12/01/2003'
AND fldDATE <= '12/31/2003'

was only so I can control the date span.

&quot;Taxes are the fees we pay for civilized society&quot; G.W.

 

[r937]

then you need to generate the months, no?

use an integers table

do a left outer join from the numbers 1 through 12 to your data, and group on the numbers

like this:

[tt]select i
, count(id) as records
from integers
left outer
join exp_tbl_order
on i = month(fldDATE)
where i between 1 and 12
and fldDATE >= '12/01/2003'
and fldDATE <= '12/31/2003'
group
by i[/tt]

here &quot;i&quot; is the name of the integer column in the integers table

mine has numbers from 0 up to 255 in it

if you stock yours with 1 through 12 you won't need the first condition in the WHERE clause


rudy

http://r937.com/

SQL Consulting

 

[vpekulas]

thanks r937

&quot;Taxes are the fees we pay for civilized society&quot; G.W.

 

[PHV]

And this ?

SELECT Count(ID) AS C_COUNT,Month(fldDate) AS C_MONTH
  FROM exp_tbl_order
 WHERE Year(fldDATE)=Year(Today)-1
 GROUP BY Month(fldDate)

Hope This Help
PH.

 

[r937]

PHV, i'm not sure if my solution was really necessary for what vpekulas wanted, i was just making sure there would be at least 12 rows in the results

whereas if the actual data is missing rows for a given month, then your solution would not inlcude that month in the results

rudy

http://r937.com/

SQL Consulting

 

[PHV]

Good catch rudy.