pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgroup by function, make SQL cleaner?
6+ messages / 4 participants
[nested] [flat]
* group by function, make SQL cleaner?
@ 2006-03-16 05:18 Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 3 replies; 6+ messages in thread
From: Bryce Nesbitt @ 2006-03-16 05:18 UTC (permalink / raw)
To: pgsql-sql
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)?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: group by function, make SQL cleaner?
@ 2006-03-16 06:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bryce Nesbitt <bryce1@obviously.com>
2 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-03-16 06:04 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: group by function, make SQL cleaner?
@ 2006-03-16 06:46 Stefan Becker <stefan@yukonho.de>
parent: Bryce Nesbitt <bryce1@obviously.com>
2 siblings, 0 replies; 6+ messages in thread
From: Stefan Becker @ 2006-03-16 06:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: group by function, make SQL cleaner?
@ 2006-03-16 17:45 Bryce Nesbitt <bryce1@obviously.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Bryce Nesbitt @ 2006-03-16 17:45 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:<br>
<blockquote cite="mid12933.1142489098@sss.pgh.pa.us" type="cite">
<pre wrap="">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.
</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 log the 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>
<td valign="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>
<pre wrap="">It is so easy to get lazy and start shrink-wrapping code to the database. That's certainly why mysql SQL tends to be so non-portable....
</pre>
</body>
</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: group by function, make SQL cleaner?
@ 2006-03-16 21:45 pgsql@yukonho.de
parent: Bryce Nesbitt <bryce1@obviously.com>
2 siblings, 0 replies; 6+ messages in thread
From: pgsql@yukonho.de @ 2006-03-16 21:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: group by function, make SQL cleaner?
@ 2006-04-03 21:58 Bryce Nesbitt <bryce1@obviously.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 0 replies; 6+ messages in thread
From: Bryce Nesbitt @ 2006-04-03 21:58 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-04-03 21:58 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-16 05:18 group by function, make SQL cleaner? Bryce Nesbitt <bryce1@obviously.com>
2006-03-16 06:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-16 17:45 ` Bryce Nesbitt <bryce1@obviously.com>
2006-04-03 21:58 ` Bryce Nesbitt <bryce1@obviously.com>
2006-03-16 06:46 ` Stefan Becker <stefan@yukonho.de>
2006-03-16 21:45 ` pgsql@yukonho.de
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox