any SQL gurus in the house?

[TdC]

I'm about to embark on a killing spree because I can't get this *^$*&$*& query right. Any help would be appreciated muchly!

I've been faffing with a postgresql database. Suppose I have a table that looks like so:

+-----------------------------------------------------------+
|ID| TIMESTAMP                | BRAND | TYPE        | ACTIVITY    |
+-----------------------------------------------------------+
|1| 2014-09-05 21:10:58.232    | Ford    | Focus        | start        |
+-----------------------------------------------------------+
|2| 2014-09-05 22:15:17.219    | Ford    | Focus        | stop        |
+-----------------------------------------------------------+
|3| 2014-09-06 07:10:43.817    | Mazda    | RX8        | start        |
+-----------------------------------------------------------+
|4| 2014-09-06 09:15:33.192    | Mazda | RX8        | stop        |
+-----------------------------------------------------------+
|5| 2014-09-07 16:10:43.817    | Ford    | Transit    | start        |
+-----------------------------------------------------------+
|6| 2014-09-07 17:15:33.192    | Ford    | Transit    | stop        |
+-----------------------------------------------------------+

I've discovered how to sum drive times per brand across the entire table, that's not that hard. Here's the thing that's driving me batshit insane: I want to select arbitrary 24 hour periods, and then sum the drive times of all the cars that happen to be in there per brand.

For some reason I get all the results back when I do something like this (and I don't know why -.- )

SELECT start.BRAND, start.TYPE, SUM(AGE(stop.TIMESTAMP, start.TIMESTAMP)) FROM cars start JOIN cars stop ON start.ACTIVITY = 'start' AND stop.ACTIVITY = 'stop' GROUP BY start.BRAND, start.TIMESTAMP HAVING ((MAX(start.TIMESTAMP) - '1 day') > start.TIMESTAMP > (MAX(start.TIMESTAMP) - '2 days') ;

I'd expect to see the drivetime of the Mazda summed and returned, but I've been getting the oddest errors. Any help would be *really* great. Cheers bluds.

 

[TdC]

never mind, I got it

 

[dysfunction]

And what was it??

 

[Aoami]

does postgresql have the datediff function?

 

[TdC]

And what was it??

I was using aggregation in the WHERE clause, which it seems you can't do. E. g. something like:

WHERE date = MAX(date) - '1 day'

None of the databases will allow it according to the internets.

 

[Overdriven]

Can :|

Assuming PL/SQL allows you to use HAVING

SELECT BLAH
FROM BLAH
WHERE BLACK
GROUP BY BLAH
HAVING COUNT(BLAH) > 5

 

[TdC]

yeah, iirc I couldn't get MAX to work in HAVING, so I do a select date order by desc limit 1 to get the highest. joy.

 

[old.Osy]

What speak is this