Thread: difficult query

difficult query

From
Jakub Ouhrabka
Date:
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




Re: difficult query

From
Ian Barwick
Date:
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





Re: difficult query

From
Jakub Ouhrabka
Date:
> > 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



Re: difficult query

From
Ian Barwick
Date:
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=2UNION
SELECT 3, COUNT(data) FROM table1 WHERE day=3UNION
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



Re: difficult query

From
Masaru Sugawara
Date:
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 notdepends on the elements of  "day" column.  But
untilits 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
UNIONALL          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




Re: difficult query

From
"Joel Burton"
Date:
> > 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
select5 ...) 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.