Thread: group by function, make SQL cleaner?
I've got a working query:
stage=# SELECT date_trunc('day',endtime),count(*)
FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
GROUP BY date_trunc('day',endtime)
ORDER BY date_trunc('day',endtime);
date_trunc | count
---------------------+-------2006-02-01 00:00:00 | 2532006-02-02 00:00:00 | 2452006-02-03 00:00:00 |
2312006-02-0400:00:00 | 3132006-02-05 00:00:00 | 2852006-02-06 00:00:00 | 1942006-02-07 00:00:00 |
2292006-02-0800:00:00 | 2392006-02-09 00:00:00 | 2502006-02-10 00:00:00 | 2452006-02-11 00:00:00 | 275
Is there a way to eliminate the ugly repeated use of
date_trunc('day',endtime)?
Bryce Nesbitt <bryce1@obviously.com> writes:
> SELECT date_trunc('day',endtime),count(*)
> FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
> GROUP BY date_trunc('day',endtime)
> ORDER BY date_trunc('day',endtime);
> Is there a way to eliminate the ugly repeated use of
> date_trunc('day',endtime)?
In this particular case you could say
... GROUP BY 1 ORDER BY 1;
"ORDER BY n" as a reference to the n'th SELECT output column is in the
SQL92 spec. (IIRC they removed it in SQL99, but we still support it,
and I think most other DBMSes do too.) "GROUP BY n" is *not* in any
version of the spec but we allow it anyway. I'm not sure how common
that notation is.
This does not work in any context except repeating a SELECT result
expression in GROUP BY or ORDER BY.
regards, tom lane
Tom Lane wrote:<br /><blockquote cite="mid12933.1142489098@sss.pgh.pa.us" type="cite"><pre wrap="">In this particular
caseyou could say
... GROUP BY 1 ORDER BY 1;
"ORDER BY n" as a reference to the n'th SELECT output column is in the
SQL92 spec. (IIRC they removed it in SQL99, but we still support it,
and I think most other DBMSes do too.) "GROUP BY n" is *not* in any
version of the spec but we allow it anyway. I'm not sure how common
that notation is.
</pre></blockquote> Thanks. Markus Bertheau also supplied this solution:<br /><pre wrap="">SELECT enddate, count(*)
FROM( SELECT date_trunc('day', endtime) AS enddate FROM eg_event WHERE
endtime >= '2006-01-01' and endtime < '2006-03-01') as foo
GROUP BY enddate
ORDER BY enddate
It brings up a question though: is there any way in Postgres to set a "quirks" or "standards" mode. Or get Postgres to
logthe compliance level of each command and command element used, e,g.:
</pre><table align="center" border="1" cellpadding="2" cellspacing="2" width="40%"><tbody><tr><td valign="top">ORDER BY
n<br/></td><td valign="top">SQL92<br /></td></tr><tr><td valign="top">GROUP BY n<br /></td><td valign="top">PSQL<br
/></td></tr><tr><tdvalign="top">(SELECT ...)<br /></td><td valign="top">SQL99<br /></td></tr><tr><td
valign="top">SELECT<br/></td><td valign="top">SQL99<br /></td></tr><tr><td valign="top">count(...)<br /></td><td
valign="top">SQL99<br/></td></tr><tr><td valign="top">date_trunc(string,...)<br /></td><td valign="top">PQSL<br
/></td></tr></tbody></table><prewrap="">It is so easy to get lazy and start shrink-wrapping code to the database.
That'scertainly why mysql SQL tends to be so non-portable....
</pre>
this should work,
#
SELECT date_trunc('day',endtime),count(*)FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'GROUP BY
1 ORDER BY 1;
hope this helps
best regards,
Stefan
Am Donnerstag, 16. März 2006 06:18 schrieb Bryce Nesbitt:
> I've got a working query:
>
> stage=# SELECT date_trunc('day',endtime),count(*)
> FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
> GROUP BY date_trunc('day',endtime)
> ORDER BY date_trunc('day',endtime);
>
> date_trunc | count
> ---------------------+-------
> 2006-02-01 00:00:00 | 253
> 2006-02-02 00:00:00 | 245
> 2006-02-03 00:00:00 | 231
> 2006-02-04 00:00:00 | 313
> 2006-02-05 00:00:00 | 285
> 2006-02-06 00:00:00 | 194
> 2006-02-07 00:00:00 | 229
> 2006-02-08 00:00:00 | 239
> 2006-02-09 00:00:00 | 250
> 2006-02-10 00:00:00 | 245
> 2006-02-11 00:00:00 | 275
>
> Is there a way to eliminate the ugly repeated use of
> date_trunc('day',endtime)?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
email: stefan@yukonho.de
tel : +49 (0)6232-497631
http://www.yukonho.de
this should work,
#
SELECT date_trunc('day',endtime),count(*)FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'GROUP BY
1 ORDER BY 1;
best regards,
Stefan
Am Donnerstag, 16. März 2006 06:18 schrieb Bryce Nesbitt:
> I've got a working query:
>
> stage=# SELECT date_trunc('day',endtime),count(*)
> FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
> GROUP BY date_trunc('day',endtime)
> ORDER BY date_trunc('day',endtime);
>
> date_trunc | count
> ---------------------+-------
> 2006-02-01 00:00:00 | 253
> 2006-02-02 00:00:00 | 245
> 2006-02-03 00:00:00 | 231
> 2006-02-04 00:00:00 | 313
> 2006-02-05 00:00:00 | 285
> 2006-02-06 00:00:00 | 194
> 2006-02-07 00:00:00 | 229
> 2006-02-08 00:00:00 | 239
> 2006-02-09 00:00:00 | 250
> 2006-02-10 00:00:00 | 245
> 2006-02-11 00:00:00 | 275
>
> Is there a way to eliminate the ugly repeated use of
> date_trunc('day',endtime)?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
email: stefan@yukonho.de
tel : +49 (0)6232-497631
http://www.yukonho.de
Bryce Nesbitt wrote:
> Tom Lane wrote:
>> In this particular case you could say
>>
>> ... GROUP BY 1 ORDER BY 1;
>>
>> "ORDER BY n" as a reference to the n'th SELECT output column is in the
>> SQL92 spec. (IIRC they removed it in SQL99, but we still support it,
>> and I think most other DBMSes do too.) "GROUP BY n" is *not* in any
>> version of the spec but we allow it anyway. I'm not sure how common
>> that notation is.
>>
>>
> Thanks. Markus Bertheau also supplied this solution:
> SELECT enddate, count(*) FROM (
> SELECT date_trunc('day', endtime) AS enddate FROM eg_event WHERE
> endtime >= '2006-01-01' and endtime < '2006-03-01') as foo
> GROUP BY enddate
> ORDER BY enddate
>
Hmm. Is there a way to specify the "n" column in a WHERE?
demo=> select p_last_name,count(*) from xx_person group by p_last_name
where 2 > 28;
ERROR: syntax error at or near "where" at character 65
LINE 1: ...name,count(*) from eg_person group by p_last_name where '3' ...
demo=> select p_last_name,count(*) from xx_person group by p_last_name
order by 2 desc limit 6;
p_last_name | count
-------------+-------
Smith | 44
Miller | 37
Lee | 35
Williams | 33
Johnson | 30
Jones | 28
(6 rows)
--
----
Visit http://www.obviously.com/