March 19, 2008 at 11:43 pm
I have a table of users and date when they logged on to a system. I am trying to count how many distinct users logged on for each day of the week. The SQL below works when there's at least a user for each day. But when there is no user for a particular day such as Sunday, I still want it to return "SUN 0 "
I learned that you can use GROUP BY ALL and it works but the "ALL" is deprecated beyond SQL 2005.
------------------------------------
SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek,
COUNT(DISTINCT UserID) AS NumberOfUser
FROM testUserLoginDuration
WHERE Archived = 0
GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3))
ORDER BY
CASE WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'MON' THEN 1
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'TUE' THEN 2
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'WED' THEN 3
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'THU' THEN 4
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'FRI' THEN 5
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SAT' THEN 6
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SUN' THEN 7
END
--------------
returns
MON 6
TUE 3
WED 5
THU 3
FRI 2
SAT 1
March 20, 2008 at 3:50 am
a very simple (but may not be the best) workaround:
create a table with the days like:
CREATE TABLE days (dayname CHAR(3) PRIMARY KEY)
INSERT INTO days VALUES ('MON')
INSERT INTO days VALUES ('TUE')
INSERT INTO days VALUES ('WED')
...
and left join this table with your results (and if you do not like nulls, then just use ISNULL(countcolumn, 0)
Regards,
Andras
March 20, 2008 at 2:16 pm
Another possibility is to use a union statement as follows:
SELECT DayOfWeek, SUM(NumberOfUser) AS [NumberOfUser]
FROM (SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek,
COUNT(DISTINCT UserID) AS NumberOfUser
FROM testUserLoginDuration
WHERE Archived = 0
GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3))
UNION SELECT 'MON' AS DayOfWeek, 0 AS NumberOfUser
UNION SELECT 'TUE' AS DayOfWeek, 0 AS NumberOfUser
UNION SELECT 'WED' AS DayOfWeek, 0 AS NumberOfUser
UNION SELECT 'THU' AS DayOfWeek, 0 AS NumberOfUser
UNION SELECT 'FRI' AS DayOfWeek, 0 AS NumberOfUser
UNION SELECT 'SAT' AS DayOfWeek, 0 AS NumberOfUser
UNION SELECT 'SUN' AS DayOfWeek, 0 AS NumberOfUser) a
GROUP BY DayOfWeek
ORDER BY CASE WHEN UPPER(DayOfWeek) = 'MON' THEN 1
WHEN UPPER(DayOfWeek) = 'TUE' THEN 2
WHEN UPPER(DayOfWeek) = 'WED' THEN 3
WHEN UPPER(DayOfWeek) = 'THU' THEN 4
WHEN UPPER(DayOfWeek) = 'FRI' THEN 5
WHEN UPPER(DayOfWeek) = 'SAT' THEN 6
WHEN UPPER(DayOfWeek) = 'SUN' THEN 7 END
March 20, 2008 at 2:39 pm
Only to show that there is more than one way to get something done.....
-- First create some test data
DECLARE @testUserLoginDuration TABLE (starttime DATETIME, userid INT)
INSERT @testUserLoginDuration
SELECT '3/3/2008',1 UNION ALL
SELECT '3/5/2008',2 UNION ALL
SELECT '3/5/2008',3 UNION ALL
SELECT '3/6/2008',4 UNION ALL
SELECT '3/7/2008',5 UNION ALL
SELECT '3/9/2008',6 UNION ALL
SELECT '3/9/2008',7;
-- use a CTE to hold the weekdays, or you could just as easily use Andras' suggestion
WITH days (daynumber, weekDay)
AS
(SELECT 1,'Monday' UNION
SELECT 2,'Tuesday' UNION
SELECT 3,'Wednesday' UNION
SELECT 4,'Thursday' UNION
SELECT 5,'Friday' UNION
SELECT 6,'Saturday' UNION
SELECT 7,'Sunday')
SELECT
DISTINCT
daynumber
,weekDay
-- now use the COUNT .. OVER .. PARTITION BY keywords
,COUNT(userid) OVER (PARTITION BY DATEPART(dw, startTime))
FROM
days AS d
LEFT JOIN @testUserLoginDuration AS t
ON d.daynumber = datepart(dw,startTime)
ORDER BY
daynumber
Or don't use the OVER .. PARTITION BY ...
I really just wanted to show you the use of that new feature in SQL 2005, as it can come in handy.
-- use a CTE to hold the weekdays, or you could just as easily use Andras' suggestion
WITH days (daynumber, weekDay)
AS
(SELECT 1,'Monday' UNION
SELECT 2,'Tuesday' UNION
SELECT 3,'Wednesday' UNION
SELECT 4,'Thursday' UNION
SELECT 5,'Friday' UNION
SELECT 6,'Saturday' UNION
SELECT 7,'Sunday')
SELECT
daynumber
,weekDay
,COUNT(userid)
FROM
days AS d
LEFT JOIN @testUserLoginDuration AS t
ON d.daynumber = datepart(dw,startTime)
GROUP BY
daynumber
,weekday
ORDER BY
daynumber
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 24, 2008 at 3:20 pm
You have to force a null value for empty days, that's what a Tally or Numbers table does well. Then just some GROUP BYs and you don't need a DISTINCT, which can be costly.
declare @LastMonday datetime;
set @LastMonday = '2008-03-24';
declare @test-2 table(
StartTime datetime,
UserID int
);
insert into @test-2( StartTime, UserID )
select @LastMonday, 1 union all
select @LastMonday, 2 union all
select @LastMonday, 3 union all
select @LastMonday, 4 union all
select @LastMonday, 5 union all
select @LastMonday, 6 union all
select @LastMonday, 1 union all
select @LastMonday, 2 union all
select @LastMonday, 3 union all
select @LastMonday, 4 union all
select @LastMonday, 5 union all
select @LastMonday, 6 union all
select DateAdd( dd, 1, @LastMonday), 1 union all
select DateAdd( dd, 1, @LastMonday), 2 union all
select DateAdd( dd, 1, @LastMonday), 1 union all
select DateAdd( dd, 1, @LastMonday), 2 union all
select DateAdd( dd, 1, @LastMonday), 3 union all
select DateAdd( dd, 2, @LastMonday), 1 union all
select DateAdd( dd, 2, @LastMonday), 2 union all
select DateAdd( dd, 2, @LastMonday), 1 union all
select DateAdd( dd, 2, @LastMonday), 2 union all
select DateAdd( dd, 2, @LastMonday), 3 union all
select DateAdd( dd, 2, @LastMonday), 4 union all
select DateAdd( dd, 2, @LastMonday), 5 union all
select DateAdd( dd, 3, @LastMonday), 1 union all
select DateAdd( dd, 3, @LastMonday), 2 union all
select DateAdd( dd, 3, @LastMonday), 3 union all
select DateAdd( dd, 4, @LastMonday), 1 union all
select DateAdd( dd, 4, @LastMonday), 2 union all
select DateAdd( dd, 4, @LastMonday), 2 union all
select DateAdd( dd, 5, @LastMonday), 1;
select case DayNum
when 1 then 'Sun'
when 2 then 'Mon'
when 3 then 'Tue'
when 4 then 'Wed'
when 5 then 'Thu'
when 6 then 'Fri'
when 7 then 'Sat'
end AS DayOfWeek,
IsNull( Count( UserID ), 0 ) AS NumberOfUsers
from (
select n.ZN as DayNum, t.UserID
from Utility..Tally n
left join @test-2 t
on n.ZN = DatePart( dw, t.StartTime )
where n.ZN between 1 and 7
group by n.ZN, t.UserID
) z
group by DayNum
order by DayNum
Then you just have to decide if you want to run different weeks together as one or separate them out.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply