• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

How to get a count based upon a grouping

I need to query a table similar to the one below. Basically I need to group the records below by the number of records
in each month. So the query would produce this result from the CustomerPurchases table. I can have the query display the month/year however how do I get the counts, of the number of records per month?
Purchase Per Month
5/2016     3
6/2016     1
7/2016     4

CustomerPurchases
1 | 2016-05-01 12:33:44:187  |  Bartermore
2 | 2016-05-13 12:30:00:000  |  Drake
3 | 2016-05-31 01:22:11:187  |  Sami
4 | 2016-06-01 12:33:44:166  |  Gabak
5 | 2016-07-01 11:28:44:144  |  Laster
6 | 2016-07-11 12:34:45:186  |  Gobo
7 | 2016-07-15 12:33:44:187  |  Darmore
8 | 2016-07-15 12:33:44:175  |  Grabson
0
brgdotnet
Asked:
brgdotnet
3 Solutions
 
HuaMin ChenSystem AnalystCommented:
Try
select substring(convert(varchar,[PurchaseDate],112),1,6) [Month],count(*) [Total]
from Tab1
group by substring(convert(varchar,[PurchaseDate],112),1,6);

Open in new window

1
 
Olaf DoschkeSoftware DeveloperCommented:
Well, in SQL terminology you don't say " I need to group the records below by the number of records
in each month", you say "I need to group the records by month and get the count.

I'd give this a try - depends on your SQL Server version:
Select Year(Purchasedate), Month(Purchasedate), Count(*) FROM CustomerPurchases
Group By Year(Purchasedate), Month(Purchasedate)

Open in new window


Bye, Olaf.

PS: YEAR() and MONTH() are available since 2008, if your SQL Server installation is older then it's time to think of an upgrade, but you can then also use datepart(year, Purchasedate) and datepart(month, Purchasedate).
1
 
Scott PletcherSenior DBACommented:
The "standard, best-practice" way of adjusting a date to a given time period -- day, week, month, year, whatever -- is this:
DATEADD(<time_period>, DATEDIFF(<time_period>, 0, Column_Date), 0)
This very efficiently strips the time and adjusts the value to the start of the relevant period.

Thus, for this case specifically, you would do something like below.  The conversion of date to char is done last because SQL is slower at handling chars than dates, so it's more efficient to delay the conversion until then.

SELECT STUFF(CONVERT(varchar(10), [Month], 101), 3, 3, '') AS Month,
    Purchase_Count
FROM (
    SELECT
        CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, PurchaseDate), 0) AS date) AS Month,
        COUNT(*) AS Purchase_Count
    FROM CustomerPurchases
    /* WHERE PurchaseDate >= '20160101' /*if you need to limit the time range considered*/ */    
    GROUP BY CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, PurchaseDate), 0) AS date)
) AS derived
ORDER BY [Month]
1
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
brgdotnetcontractorAuthor Commented:
Thank you.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
brgdotnet, if your problem has been solved please choose the comment or comments that helped you out.
Cheers
1
 
brgdotnetcontractorAuthor Commented:
:)
0
 
HuaMin ChenSystem AnalystCommented:
Hi Brgdotnet,
Please confirm that you have no more questions to this thread. Thanks
1
 
HuaMin ChenSystem AnalystCommented:
The previous given advice should have resolved the author's original question.
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now