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

 sql table
TheWebGuy38Asked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
try this

SELECT convert(varchar(10),TimeOccured, 101), COUNT(TimeOccured) AS IncidentCount
FROM  IncidentReport
GROUP BY convert(varchar(10),TimeOccured, 101)
ORDER BY IncidentCount DESC
0
 
Pratima PharandeCommented:

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
0
 
jimyXCommented:
You can think of something like this:
Select ROWNUM, IncidentDate, Count(IncidentDate) from YourTable Group by IncidentDate order by IncidentDate
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
TheWebGuy38Author Commented:
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
0
 
jimyXCommented:
Try:

SELECT *, ROW_Number, TimeOccured AS Expr1, COUNT(TimeOccured) AS Expr2
FROM  IncidentReport
GROUP BY TimeOccured
ORDER BY TimeOccured
0
 
TheWebGuy38Author Commented:
same thing, having a problem at ROW_Number

what is rownum anyways? a variable to pass data back?
0
 
jimyXCommented:
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
0
 
Pratima PharandeCommented:

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
0
 
TheWebGuy38Author Commented:
err,, no ROW_NUMBER is don't think is anything
and I'm passing it into a dataset, will this syntax work like that?
0
 
TheWebGuy38Author Commented:
trying it this way, still blowing up
SELECT TimeOccured
FROM  (SELECT TimeOccured, COUNT(*)
               FROM   IncidentReport
               GROUP BY DateStamp)
ORDER BY incidents
0
 
Pratima PharandeCommented:
try thia

SELECT X.TimeOccured,X.incidents
FROM  (SELECT TimeOccured, COUNT(*) as incidents
               FROM   IncidentReport
               GROUP BY DateStamp) X
ORDER BY X.incidents
0
 
jimyXCommented:
What about this:
Select TimeOccured, Count(TimeOccured)
From IncidentReport
Group by TimeOccured
Order by TimeOccured
0
 
TheWebGuy38Author Commented:
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
0
 
SharathData EngineerCommented:
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

Open in new window

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

Open in new window

0
 
jimyXCommented:
Set the field TimeOccured datatype to Date.
0
 
TheWebGuy38Author Commented:
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?

0
 
Pratima PharandeCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.