CODEWITHSUNDEEP
sqlsql-serversql-server-2008group-by
viv_acious
viv_acious

Reputation: 2489

SQL - count occurrences in a column by DAY

I have a table as follows:

and I want to count the occurrences of say "ab" and "cd" in the column PageURL and GROUP by DAY (i.e. no matter how many occurrences for the day, it's count as 1).

ID  User  Activity  PageURL  ActDateTime
 1  Me    act1      abcd     2013-01-17 19:09:01.040
 2  Me    act2      cdab     2013-01-17 19:09:06.613
 3  You   act2      xyza     2013-01-30 16:10:50.177
 4  Me    act3      xyab     2013-01-30 10:35:09.037

I want to have 2 columns...1 for count of "ab" and 1 for count of "cd".

In the above example, there are 3 counts for "ab" but I will only count as 2 because the first 2 occurred on the SAME day (so count as 1).

Again, there are 2 counts for "cd" in PageURL column but I want to only count as 1 because occurred on same day too.

Furthermore, I want to group by Month-Year i.e. Jan-12, Feb-12, March-12, April-12 ...etc.

Would really appreciate some assistance and advice. Thank you!

This is what I've done so far (but it does NOT take into the account of grouping by DAY)

SELECT USER,
       department,
       activity,
       [MonthYear] = DATENAME(mm, ActDateTime)+ ' - ' + 
                                  DATENAME(yy, actdatetime), 
       [ab] = sum(case when pageURL like '%ab%' THEN 1 else 0 END), 
       [cd]= sum(CASE WHEN pageURL LIKE '%cd%'THEN 1 ELSE 0 END)
FROM activityLog
GROUP BY USER,
         department,
         activity,
         DATENAME(mm, ActDateTime)+ ' - ' + 
         DATENAME(yy, ActDateTime)
ORDER BY USER,
         department,
         activity,
         DATENAME(mm, ActDateTime)+ ' - ' + DATENAME(yy, ActDateTime)

Upvotes: 0

Views: 2645

Answers (3)

Justin
Justin

Reputation: 9724

First query to get counts by day:

SELECT cast(ActDateTime AS Date) AS DateOnly,
       CASE
           WHEN SUM(CASE WHEN PageUrl LIKE '%ab%' THEN 1 ELSE 0 END) >=1 THEN 1
           ELSE 0
       END AS ABCount,
       CASE
           WHEN SUM(CASE WHEN PageUrl LIKE '%cd%' THEN 1 ELSE 0 END) >=1 THEN 1
           ELSE 0
       END AS CDCount
FROM activityLog
GROUP BY cast(ActDateTime AS Date)

Second Query to get your desired output:

SQLFIDDLEExample

SELECT DATENAME(month, a.ActDateTime)+ ' - ' + DATENAME(yy, a.ActDateTime)
      ,SUM(a.ABCount) as ABCount
      ,SUM(a.CDCount) as CDCount
FROM (SELECT cast(ActDateTime AS Date) AS ActDateTime,
       CASE
           WHEN SUM(CASE WHEN PageUrl LIKE '%ab%' THEN 1 ELSE 0 END) >=1 THEN 1
           ELSE 0
       END AS ABCount,
       CASE
           WHEN SUM(CASE WHEN PageUrl LIKE '%cd%' THEN 1 ELSE 0 END) >=1 THEN 1
           ELSE 0
       END AS CDCount
FROM activityLog
GROUP BY cast(ActDateTime AS Date))a
GROUP BY DATENAME(month, a.ActDateTime)+ ' - ' + DATENAME(yy, a.actdatetime)
ORDER BY DATENAME(month, a.ActDateTime)+ ' - ' + DATENAME(yy, a.actdatetime)

Result:

|       COLUMN_0 | ABCOUNT | CDCOUNT |
--------------------------------------
| January - 2013 |       2 |       1 |

Upvotes: 1

Dan Metheus
Dan Metheus

Reputation: 1438

This should work to create your counts I think:

SELECT
  cast(ActDateTime as Date) as DateOnly, 
  SUM(CASE WHEN PageUrl Like '%ab%' THEN 1 ELSE 0 END) as ABCount,
  SUM(CASE WHEN PageUrl Like '%cd%' THEN 1 ELSE 0 END) as CDCount
FROM Table1 
GROUP BY cast(ActDateTime as Date)

SQL Fiddle

Upvotes: 2

Dale M
Dale M

Reputation: 2473

SELECT Thing
      ,COUNT(*) NumDays
FROM  (SELECT LEFT(PageURL, 2) AS Thing
       ,DateTime
       FROM table
       ALL
       SELECT RIGHT(PageURL, 2)
             ,DateTime
       FROM table) UniqueDays

This will not work as is because of the key words you have used in your question.

In general it is easier to use the actual column/table names so the answer can reflect these - it avoids confusion.

Upvotes: 0

Related Questions