CODEWITHSUNDEEP
sqlms-access
Duffie
Duffie

Reputation: 97

SQL Simple Group By causing duplicates sections

I have a SQL query that is causing me trouble. The SQL That I have so far is the following: 

SELECT Dated, [Level 1 reason], SUM(Duration) AS Mins
FROM Downtime
GROUP BY Dated, [Level 1 reason]

Now the problem I am having is that the results include multiple reasons, rather than being grouped together as I require. An example of the problem results is the following: 

1/2/2013 10:02:00 AM    Westport    2
1/2/2013 10:17:00 AM    Westport    9
1/2/2013 10:48:00 AM    Engineering 5
1/2/2013 11:01:00 AM    Facilities  6

The intended result is that there be a single Westport group for a date. The query also needs to handle multiple dates, but those weren't included in the snippet for readability.

Thanks for any help. I know it's some simple reason, but I can't figure it out.

**EDIT IN: sorry, I am performing this in Access. Removing the Group by Dated results in an error in Access. I am unsure what to make of it

"You Tried to excecute a query that does not include the specified expression 'Dated as part of an aggregate function."**

D Stanley solved my question with the following query

SELECT DateValue(Dated) AS Dated, [Level 1 reason], SUM(Duration) AS Mins
FROM Downtime
GROUP BY DateValue(Dated), [Level 1 reason]

Upvotes: 0

Views: 438

Answers (3)

Ondrej Svejdar
Ondrej Svejdar

Reputation: 22074

I guess in access it would be:

SELECT CDate(Int(Dated)) , [Level 1 reason],
   SUM(Duration) AS Mins
FROM Downtime
GROUP BY CDate(Int(Dated)) , [Level 1 reason];

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

It seems like you want to remove the time component. How to do that varies between database systems. In SQL Server it would be:

SELECT DATEADD(day,DATEDIFF(day,0,Dated),0), [Level 1 reason],
       SUM(Duration) AS Mins
FROM Downtime
GROUP BY DATEADD(day,DATEDIFF(day,0,Dated),0), [Level 1 reason]

This works because 0 can be implicitly converted to a date (01/01/1900 at midnight), and DATEADD and DATEDIFF only work in integral parts of the datepart (here, day). So, this is "how many complete days have occurred since 01/01/1900 at midnight?" and "Let's add that same number of days onto 01/01/1900 at midnight" - which gives us the same date as the value we started with, but always at midnight.

For Access, I think you have to quote the datepart (day becomes "d"). I'm not sure if the 0 implicit conversion still works - but you can just substitute any constant date in all for places I've used a 0 above, something like:

SELECT DATEADD("d",DATEDIFF("d","01/01/1900",Dated),"01/01/1900"),
       [Level 1 reason],
       SUM(Duration) AS Mins
FROM Downtime
GROUP BY DATEADD("d",DATEDIFF("d","01/01/1900",Dated),"01/01/1900"),
         [Level 1 reason]

Upvotes: 0

D Stanley
D Stanley

Reputation: 152614

In Access you ace use the DateValue function to remove the time from a date column:

SELECT DateValue(Dated) Dated, [Level 1 reason], SUM(Duration) AS Mins
FROM Downtime
GROUP BY DateValue(Dated), [Level 1 reason]

Upvotes: 2

Related Questions