db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: how to count total number of records in each group , when using group by statement.
Date Mon, 28 Nov 2005 13:10:19 GMT
Correction: Since year(), month() etc is not standard SQL, but scalar
functions defined in JDBC/ODBC escape syntax,  the portable syntax for this would be:

instead of

> ij> select h,count(*) from (select hour(d) from x) as t(h) group by h;

write:
select h,count(*) from (select {fn hour(d)} from x) as t(h) group by h;

and instead of

> ij> select year(d),month(d),day(d),hour(d),minute(d),second(d) from x;

write:
select {fn year(d)},{fn month(d)},{fn day(d)},{fn hour(d)},{fn minute(d)},{fn second(d)} from
x;

--------------------
Furthermore: year(), month etc in Derby returns 0 if the datetime
values is NULL, so if the column contains NULL values the first query
is wrong and should be

select h,count(*) from (select {fn hour(d)} from x where d is not null) as t(h) group by h;

However: if hour(d) is to behave like standard SQL extract(hour from
d), then hour(d) should be NULL when d is null. I think there is a bug
in Derby here. Anyway, if hour(d) *had* returned NULL, the proper
query would have been

select h,count(h) from (select {fn hour(d)} from x) as t(h) group by h;

since count(h) should ignore NULL values while count(*) should not.
-- 
Bernt Marius Johnsen, Database Technology Group, 
Sun Microsystems, Trondheim, Norway

Mime
View raw message