pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
difficult query
6+ messages / 4 participants
[nested] [flat]

* difficult query
@ 2002-05-04 14:00  Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
  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 15:44  Ian Barwick <barwick@gmx.net>
  parent: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
  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>
  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 18:19  Ian Barwick <barwick@gmx.net>
  parent: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
  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-05 05:43  Masaru Sugawara <rk73@sea.plala.or.jp>
  parent: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
  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

* Re: difficult query
@ 2002-05-05 06:19  Joel Burton <joel@joelburton.com>
  parent: Ian Barwick <barwick@gmx.net>
  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


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