pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddifficult query
6+ messages / 4 participants
[nested] [flat]
* difficult query
@ 2002-05-04 14:00 Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-05-04 15:44 ` Re: difficult query Ian Barwick <barwick@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Jakub Ouhrabka @ 2002-05-04 14:00 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'd like to ask how to write sql query returning following results without
using any other table than table1 (e.g. without outer join with table
containing all possible day numbers...)
table1:
day | data
------------
1 | x
1 | x
2 | x
4 | x
4 | x
I'd like to write query returning this:
query results:
day | number of records for a day
----------------------
1 | 2
2 | 1
3 | 0 <--- !!!
4 | 2
Is it possible to do this in sql?
thanks a lot, kuba
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: difficult query
2002-05-04 14:00 difficult query Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
@ 2002-05-04 15:44 ` Ian Barwick <barwick@gmx.net>
0 siblings, 0 replies; 6+ messages in thread
From: Ian Barwick @ 2002-05-04 15:44 UTC (permalink / raw)
To: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>; pgsql-sql
On Saturday 04 May 2002 16:00, Jakub Ouhrabka wrote:
> Hi,
>
> I'd like to ask how to write sql query returning following results without
> using any other table than table1 (e.g. without outer join with table
> containing all possible day numbers...)
(...)
> I'd like to write query returning this:
>
> query results:
> day | number of records for a day
> ----------------------
> 1 | 2
> 2 | 1
> 3 | 0 <--- !!!
> 4 | 2
>
> Is it possible to do this in sql?
Basically, no. (You might be able to do it with the help
of a user-defined function though).
Why do you want to do this?
Ian Barwick
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: difficult query
@ 2002-05-04 17:34 Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-05-04 18:19 ` Re: difficult query Ian Barwick <barwick@gmx.net>
2002-05-05 05:43 ` Re: difficult query Masaru Sugawara <rk73@sea.plala.or.jp>
0 siblings, 2 replies; 6+ messages in thread
From: Jakub Ouhrabka @ 2002-05-04 17:34 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: pgsql-sql
> > Is it possible to do this in sql?
>
> Basically, no. (You might be able to do it with the help
> of a user-defined function though).
>
> Why do you want to do this?
I'm curious. I've been on a sql course long time ago where the tutor
mentioned similiar problem as something special for sql smarties... And
yesterday when I faced this problem (which can be solved perfectly with
the outer join) I wasn't able to remember the solution... If there is
any... Just out of curiosity...
thanks, kuba
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: difficult query
2002-05-04 17:34 Re: difficult query Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
@ 2002-05-04 18:19 ` Ian Barwick <barwick@gmx.net>
2002-05-05 06:19 ` Re: difficult query Joel Burton <joel@joelburton.com>
1 sibling, 1 reply; 6+ messages in thread
From: Ian Barwick @ 2002-05-04 18:19 UTC (permalink / raw)
To: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>; +Cc: pgsql-sql
On Saturday 04 May 2002 19:34, Jakub Ouhrabka wrote:
> > > Is it possible to do this in sql?
> >
> > Basically, no. (You might be able to do it with the help
> > of a user-defined function though).
> >
> > Why do you want to do this?
>
> I'm curious. I've been on a sql course long time ago where the tutor
> mentioned similiar problem as something special for sql smarties... And
> yesterday when I faced this problem (which can be solved perfectly with
> the outer join) I wasn't able to remember the solution... If there is
> any... Just out of curiosity...
well, you could do it like this I suppose:
SELECT 1 AS day, COUNT(data) FROM table1 WHERE day=1
UNION
SELECT 2, COUNT(data) FROM table1 WHERE day=2
UNION
SELECT 3, COUNT(data) FROM table1 WHERE day=3
UNION
SELECT 4, COUNT(data) FROM table1 WHERE day=4
(and so on ad infinitum)
although I expect E.F. Codd will be turning in his grave ;-)
Ian Barwick
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: difficult query
2002-05-04 17:34 Re: difficult query Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-05-04 18:19 ` Re: difficult query Ian Barwick <barwick@gmx.net>
@ 2002-05-05 06:19 ` Joel Burton <joel@joelburton.com>
0 siblings, 0 replies; 6+ messages in thread
From: Joel Burton @ 2002-05-05 06:19 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>; +Cc: pgsql-sql
> > I'm curious. I've been on a sql course long time ago where the tutor
> > mentioned similiar problem as something special for sql smarties... And
> > yesterday when I faced this problem (which can be solved perfectly with
> > the outer join) I wasn't able to remember the solution... If there is
> > any... Just out of curiosity...
>
> well, you could do it like this I suppose:
>
> SELECT 1 AS day, COUNT(data) FROM table1 WHERE day=1
> UNION
> SELECT 2, COUNT(data) FROM table1 WHERE day=2
> UNION
> SELECT 3, COUNT(data) FROM table1 WHERE day=3
> UNION
> SELECT 4, COUNT(data) FROM table1 WHERE day=4
>
> (and so on ad infinitum)
>
> although I expect E.F. Codd will be turning in his grave ;-)
A little better, perhaps, but still a true hack:
select alldays.d, sum(c) from
(select 1 as d union all
select 2 union all
select 3 union all
select 4 union all
select 5 ...) as alldays
left outer join d using (d) group by alldays.d;
If you could write a function in plpgsql that returned a query result, you
could use that as the from clause rather than the long union.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: difficult query
2002-05-04 17:34 Re: difficult query Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
@ 2002-05-05 05:43 ` Masaru Sugawara <rk73@sea.plala.or.jp>
1 sibling, 0 replies; 6+ messages in thread
From: Masaru Sugawara @ 2002-05-05 05:43 UTC (permalink / raw)
To: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>; +Cc: pgsql-sql
On Sat, 4 May 2002 19:34:03 +0200 (CEST)
Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz> wrote:
> > Basically, no. (You might be able to do it with the help
> > of a user-defined function though).
I also think so.
> yesterday when I faced this problem (which can be solved perfectly with
> the outer join) I wasn't able to remember the solution... If there is
> any... Just out of curiosity...
I wouldn't think that that can be always solved. Whether it can be or not
depends on the elements of "day" column. But until its aggregation
will lack two successive numbers or more, the following query is useful,
I guess.
CREATE VIEW v_table1 AS
SELECT tt.day , COUNT(tt.data) AS data
FROM (SELECT t.day, t.data FROM table1 AS t
UNION ALL
SELECT 1, NULL -- indispensable if there's no row of day=1.
)AS tt
GROUP BY tt.day
;
SELECT t0.day, t0.data
FROM v_table1 AS t0
UNION
SELECT t1.day - 1 , 0
FROM v_table1 AS t1, v_table1 AS t2
WHERE t1.day > t2.day
GROUP BY t1.day
HAVING t1.day > MAX(t2.day) + 1
;
In addition, until its aggregation will lack THREE ones or more, ...
SELECT t0.day, t0.data
FROM v_table1 AS t0
UNION
SELECT t1.day - 1, 0
FROM ...
UNION
SELECT t3.day - 2, 0
FROM ...
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-05-05 06:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-04 14:00 difficult query Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-05-04 15:44 ` Ian Barwick <barwick@gmx.net>
2002-05-04 17:34 Re: difficult query Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-05-04 18:19 ` Ian Barwick <barwick@gmx.net>
2002-05-05 06:19 ` Joel Burton <joel@joelburton.com>
2002-05-05 05:43 ` Masaru Sugawara <rk73@sea.plala.or.jp>
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