SQL problem in Access - Group by and order by Month name

[AllMememe]

I need to get the name of each month that an expiry date exists and the number of expiry dates per month.

SELECT Format(LicenceExp,'mmm') as Month, count (*) as Occ
from Licence
group by Format(LicenceExp,'mmm')
order by Format(LicenceExp,'mmm') DESC

Results:

Dec 1
Jun 1
Oct 2
Jul 4
May 2


This gets the month name, the occurrence and groups by the month but does not order by month.
Is there anyway for all months to show, even if there is no expiry date for that month and just have 0 occurrences?

At the moment I am really only worried about ordering by date please! Anything else would be a bonus!

Thanks in advance!

 

[PHV]

SELECT Format(LicenceExp,'mmm') as Month, count (*) as Occ
from Licence
group by Format(LicenceExp,'mmm'), Month(LicenceExp)
order by Month(LicenceExp) DESC


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

SELECT Format(LicenceExp,'mmm') AS [Month], Count(*) AS Occ
FROM Licence
GROUP BY Format(LicenceExp,'mmm'), Month([LicenceExp])
ORDER BY Month([LicenceExp]);

If you want all the months, you must have a table or query that has all the months that can be used in a left or right join.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Ho are ya AllMememe . . .

At best you need to [blue]order by[/blue] the integer eqiuivalent of each month, instead of its text equivalent. There may also be a problem ... in as much each year is concerned. Assuming your talking the [blue]current year[/blue] ... I see:

[blue]   SELECT  Format(LicenceExp,'mmm') as Month, count (*) as Occ, Month(LicenceExp) as intMONTH
   FROM Licence
   GROUP BY Format(LicenceExp,'mmm')
   ORDER BY  Month(LicenceExp) DESC[/BLUE]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[AllMememe]

All the above worked perfectly thank you!! I'm not too concerned about all months for now but thanks dhookom, I'll check out having a months table.

 

[AllMememe]

Apologies for bothering you again but how can I get the above to only include dates in 2011? Thanks

I have:


SELECT Format(LicenceExp,'mmm') AS [Month], Format(LicenceExp, 'yyyy') as [Year], Count(*) AS Occ
FROM Licence
GROUP BY Format(LicenceExp,'mmm'), Month([LicenceExp]), Format(LicenceExp, 'yyyy'), Year ([LicenceExp])
ORDER BY Month([LicenceExp])

but can't seem to only select 2011.

 

[PHV]

WHERE Year(LicenceExp)=2011

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886