CODEWITHSUNDEEP
sql
NDraskovic
NDraskovic

Reputation: 706

Counting the number of occurrences of a certain month in SQL

I have a complex problem to solve with SQL query. I need to generate a report that gives a numerical value which represents a number of actions that users performed in each month of the year. For instance, if User1 performed the specified action in January and July, the query needs to return numbers 1 in column called 'January' and 'July' and 0 in columns that represent other month's. The table that I'm pulling the data from has only one column that contains the date and time on which the certain action was taken (this is the only column relevant to this specific problem). I need to make a SQL query that will return this informations for each user in a certain period. Please help, and if you need any more informations please tell me and I will provide it. Thanks

The structure of the result should be like this: User Jan Feb ..... Dec UID 1 0 1

I need this for every user that appears in the selected period. My application is using SQL server 2005.

Upvotes: 1

Views: 4112

Answers (3)

Andomar
Andomar

Reputation: 238058

select  datepart(month, dateColumn) as Month
,       count(*) as NumberOfActions
from    Actions
group by
        datepart(month, dateColumn)

Upvotes: 2

Ciarán
Ciarán

Reputation: 3057

This will return the data in rows but I think the OP is actually asking for it in columns. You will need a PIVOT statement for this, something like this...

Assuming...

Create Table TestPivot (UserName Varchar(10), DateColumn DateTime);

Then...

Select *
  From (Select UserName, DateName(Month, DateColumn) As Month_Name, Count(*) As Month_Count
          From TestPivot
         Group By UserName, DateName(Month, DateColumn)) As SourcePart
  Pivot (Sum(Month_Count)
         For Month_Name In (January, February, March, April, May, June, 
                            July, August, September, October, November, December)) As PivotPart

Upvotes: 0

David Sigley
David Sigley

Reputation: 1198

The above query is correct, Though this will only return a number for the month.

I would do the following.

SELECT  dateparth(month, dateColumn) as Month, count(*) as NumberOfActions
FROM Actions
GROUP BY Month

And then (if using php) consider mktime() and date() functions. Like this:

/* The year doesn't matter. we are just trying to format this in to a date */
    $date = mktime(0, 0, 0, $row->Month, 1, 2012); 

/* This will display 'Jan, Feb, Mar ...' respectively */
    $dateText = date('M', $date);

If using another language then just google alternatives. It'll be the same principle .. just a different syntax.

Hope this helps.

Upvotes: 0

Related Questions