Обсуждение: difficult query
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
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
> > 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
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
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
> > 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.