October 28, 2003 at 4:39 pm
I am having trouble with a query and need more eyes on this problem.
I have a query:
selectdatepart(dw, logindate) as 'dow',
count(*) as number
fromlogon
groupby datepart(dw, logindate)
orderby datepart(dw, logindate)
This works just fine to get an aggregate count of items per hour.
This is what I tried with a union and this does not work as the aggregate portions are wrong:
selectdatepart(hh, logindate) as 'hour',
count(*) as number
fromlogon
union
selectdatepart(hh, logindate) as 'hour',
count(*) as number
fromlogon_archive
groupby datepart(hh, logindate)
orderby datepart(hh, logindate)
How would I fix this to gove me one set of results aggregated together?
Ed
October 28, 2003 at 5:29 pm
You are missing your GROUP BY statement in the first query for the union. However I don't think that really gives you what you want. Try this...
SELECT [hour], COUNT(*) as [number]
FROM (SELECT DATEPART(hh, logindate) as [hour]
FROM logon
UNION
SELECT DATEPART(hh, logindate) as [hour]
FROM logon_archive) a
GROUP BY [hour]
ORDER BY [hour]
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 29, 2003 at 7:33 am
I don't know if there are duplicate rows in the resultset of the union of the logon and logon_archive tables, but you might want to use UNION ALL so that duplicates are not removed.
From BOL...
UNION is specified as:
select_statement UNION [ALL] select_statement
By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.
October 29, 2003 at 10:03 am
That is exactly right. With the query as it was it only returned 24 rows with a count of 1 for each. The union all did the trick and it returns all rows with total counts.
Thanks all for the assistance!
Ed
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply