Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query - Group by day

Posted on 2011-03-16
17
Medium Priority
?
642 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:TheWebGuy38
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 5
  • +1
17 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154140

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
 
LVL 24

Expert Comment

by:jimyX
ID: 35154152
You can think of something like this:
Select ROWNUM, IncidentDate, Count(IncidentDate) from YourTable Group by IncidentDate order by IncidentDate
0
 

Author Comment

by:TheWebGuy38
ID: 35154175
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 24

Expert Comment

by:jimyX
ID: 35154186
Try:

SELECT *, ROW_Number, TimeOccured AS Expr1, COUNT(TimeOccured) AS Expr2
FROM  IncidentReport
GROUP BY TimeOccured
ORDER BY TimeOccured
0
 

Author Comment

by:TheWebGuy38
ID: 35154198
same thing, having a problem at ROW_Number

what is rownum anyways? a variable to pass data back?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35154210
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154224

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
 

Author Comment

by:TheWebGuy38
ID: 35154261
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
 

Author Comment

by:TheWebGuy38
ID: 35154282
trying it this way, still blowing up
SELECT TimeOccured
FROM  (SELECT TimeOccured, COUNT(*)
               FROM   IncidentReport
               GROUP BY DateStamp)
ORDER BY incidents
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154295
try thia

SELECT X.TimeOccured,X.incidents
FROM  (SELECT TimeOccured, COUNT(*) as incidents
               FROM   IncidentReport
               GROUP BY DateStamp) X
ORDER BY X.incidents
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35154304
What about this:
Select TimeOccured, Count(TimeOccured)
From IncidentReport
Group by TimeOccured
Order by TimeOccured
0
 

Author Comment

by:TheWebGuy38
ID: 35154343
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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 35154355
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35154371
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
 
LVL 24

Expert Comment

by:jimyX
ID: 35154405
Set the field TimeOccured datatype to Date.
0
 

Author Comment

by:TheWebGuy38
ID: 35154625
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154694
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question