TheWebGuy38
asked on
SQL Query - Group by day
Hi,
I'm trying to figure out in my head how to write a query, but I can't seem to think up the logic.
What I want to do is group a days incidents up and count them. then I want to list the data by
Top incidents by day. if that makes sense. example.
Day 1 12/10/2010 - 45 incidents
Day 2 12/11/2010 - 30 incidents
Day 3 12/12/2010- 15 incidents
etc
If anyone knows what this logic would look like I'd appreciate it.
attached is a pic of the table
I'm trying to figure out in my head how to write a query, but I can't seem to think up the logic.
What I want to do is group a days incidents up and count them. then I want to list the data by
Top incidents by day. if that makes sense. example.
Day 1 12/10/2010 - 45 incidents
Day 2 12/11/2010 - 30 incidents
Day 3 12/12/2010- 15 incidents
etc
If anyone knows what this logic would look like I'd appreciate it.
attached is a pic of the table
You can think of something like this:
Select ROWNUM, IncidentDate, Count(IncidentDate) from YourTable Group by IncidentDate order by IncidentDate
Select ROWNUM, IncidentDate, Count(IncidentDate) from YourTable Group by IncidentDate order by IncidentDate
ASKER
so something like this?
SELECT *, ROWNUM, TimeOccured AS Expr1, COUNT(TimeOccured) AS Expr2
FROM IncidentReport
GROUP BY TimeOccured
ORDER BY TimeOccured
Although it's having a problem recognizing ROWNUM
SELECT *, ROWNUM, TimeOccured AS Expr1, COUNT(TimeOccured) AS Expr2
FROM IncidentReport
GROUP BY TimeOccured
ORDER BY TimeOccured
Although it's having a problem recognizing ROWNUM
Try:
SELECT *, ROW_Number, TimeOccured AS Expr1, COUNT(TimeOccured) AS Expr2
FROM IncidentReport
GROUP BY TimeOccured
ORDER BY TimeOccured
SELECT *, ROW_Number, TimeOccured AS Expr1, COUNT(TimeOccured) AS Expr2
FROM IncidentReport
GROUP BY TimeOccured
ORDER BY TimeOccured
ASKER
same thing, having a problem at ROW_Number
what is rownum anyways? a variable to pass data back?
what is rownum anyways? a variable to pass data back?
It puts a serial number to your rows (1, 2, 3, ...):
OK, let's try this please:
SELECT *, ROW_NUMBER() OVER (ORDER BY TimeOccured) AS n, TimeOccured AS Expr1, COUNT(TimeOccured) AS Expr2
FROM IncidentReport
GROUP BY TimeOccured
ORDER BY TimeOccured
OK, let's try this please:
SELECT *, ROW_NUMBER() OVER (ORDER BY TimeOccured) AS n, TimeOccured AS Expr1, COUNT(TimeOccured) AS Expr2
FROM IncidentReport
GROUP BY TimeOccured
ORDER BY TimeOccured
order by will not work with gorup by
Select ROW_NUMBER() OVER (ORDER BY TimeOccured) AS n,X.TimeOccured ,x.incedences
SELECT TimeOccured , TimeOccured AS Expr1, COUNT(TimeOccured) AS incedences
FROM IncidentReport
GROUP BY TimeOccured ) X
ORDER BY X.TimeOccured
ASKER
err,, no ROW_NUMBER is don't think is anything
and I'm passing it into a dataset, will this syntax work like that?
and I'm passing it into a dataset, will this syntax work like that?
ASKER
trying it this way, still blowing up
SELECT TimeOccured
FROM (SELECT TimeOccured, COUNT(*)
FROM IncidentReport
GROUP BY DateStamp)
ORDER BY incidents
SELECT TimeOccured
FROM (SELECT TimeOccured, COUNT(*)
FROM IncidentReport
GROUP BY DateStamp)
ORDER BY incidents
try thia
SELECT X.TimeOccured,X.incidents
FROM (SELECT TimeOccured, COUNT(*) as incidents
FROM IncidentReport
GROUP BY DateStamp) X
ORDER BY X.incidents
SELECT X.TimeOccured,X.incidents
FROM (SELECT TimeOccured, COUNT(*) as incidents
FROM IncidentReport
GROUP BY DateStamp) X
ORDER BY X.incidents
What about this:
Select TimeOccured, Count(TimeOccured)
From IncidentReport
Group by TimeOccured
Order by TimeOccured
Select TimeOccured, Count(TimeOccured)
From IncidentReport
Group by TimeOccured
Order by TimeOccured
ASKER
ok, getting somewhere
this executes
SELECT TimeOccured, COUNT(TimeOccured) AS IncidentCount
FROM IncidentReport
GROUP BY TimeOccured
ORDER BY IncidentCount DESC
but something is wrong, I think maybe it's not getting the full day, or maybe I have to include the day/time and not just the date
this executes
SELECT TimeOccured, COUNT(TimeOccured) AS IncidentCount
FROM IncidentReport
GROUP BY TimeOccured
ORDER BY IncidentCount DESC
but something is wrong, I think maybe it's not getting the full day, or maybe I have to include the day/time and not just the date
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are working in SQL Server 2008, try this.
SELECT CONVERT(DATE,TimeOccured) AS TimeOccured,
COUNT(TimeOccured) AS IncidentCount
FROM IncidentReport
GROUP BY CONVERT(DATE,TimeOccured)
ORDER BY IncidentCount DESC
If SQL Server 2005 or less, try this.SELECT DATEADD(dd,0,DATEDIFF(dd,0,TimeOccured)) AS TimeOccured,
COUNT(TimeOccured) AS IncidentCount
FROM IncidentReport
GROUP BY DATEADD(dd,0,DATEDIFF(dd,0,TimeOccured))
ORDER BY IncidentCount DESC
Set the field TimeOccured datatype to Date.
ASKER
OH YA!!!!!!!!!!
pratima, you the man!!!
this works great!
SELECT TOP (100) CONVERT(varchar(10), TimeOccured, 101) AS TimeOccuredDay, COUNT(TimeOccured) AS IncidentCount
FROM IncidentReport
GROUP BY CONVERT(varchar(10), TimeOccured, 101)
ORDER BY IncidentCount DESC
I only have one last questio. I'm trying to pull IncidentReportID
but I try this
SELECT TOP (100) CONVERT(varchar(10), TimeOccured, 101) AS TimeOccuredDay, COUNT(TimeOccured) AS IncidentCount, IncidentReportID
FROM IncidentReport
GROUP BY CONVERT(varchar(10), TimeOccured, 101)
ORDER BY IncidentCount DESC
but it blows up. is this possible to do?
pratima, you the man!!!
this works great!
SELECT TOP (100) CONVERT(varchar(10), TimeOccured, 101) AS TimeOccuredDay, COUNT(TimeOccured) AS IncidentCount
FROM IncidentReport
GROUP BY CONVERT(varchar(10), TimeOccured, 101)
ORDER BY IncidentCount DESC
I only have one last questio. I'm trying to pull IncidentReportID
but I try this
SELECT TOP (100) CONVERT(varchar(10), TimeOccured, 101) AS TimeOccuredDay, COUNT(TimeOccured) AS IncidentCount, IncidentReportID
FROM IncidentReport
GROUP BY CONVERT(varchar(10), TimeOccured, 101)
ORDER BY IncidentCount DESC
but it blows up. is this possible to do?
SELECT TOP (100) CONVERT(varchar(10), TimeOccured, 101) AS TimeOccuredDay, COUNT(TimeOccured) AS IncidentCount, IncidentReportID
FROM IncidentReport
GROUP BY CONVERT(varchar(10), TimeOccured, 101),IncidentReportID
ORDER BY IncidentCount DESC
FROM IncidentReport
GROUP BY CONVERT(varchar(10), TimeOccured, 101),IncidentReportID
ORDER BY IncidentCount DESC
You need to group by day on Datestamp field ? right?
try this
Select X.DateStamp ,X.incidents
From (
Select DateStamp , count(*) as incidents from IncidentReport
Group by DateStamp ) X
order by X.incidents desc
OR TRY THIS
Select X.DateStamp ,X.incidents
From (
Select DateStamp , count(*) as incidents from IncidentReport
Group by DateStamp ) X
order by X.incidents asc