Thread: sql question regarding count(*)
When does count(*) returns "o rows" ? and when does it return 1 row value being 0. tradein_clients=# SELECT count(*) from public.eyp_listing where sno> 0 and amount> 0 group by sno,branch,edition having count(distinct userid) > 1 ;count ------- (0 rows) tradein_clients=# tradein_clients=# tradein_clients=# SELECT count(*) from public.users where userid=-1;count ------- 0 (1 row) tradein_clients=# -- Regds Mallah ---------------------------------------- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
Rajesh Kumar Mallah wrote: > When does count(*) returns "o rows" ? > and when does it return 1 row value being 0. > > tradein_clients=# SELECT count(*) from public.eyp_listing where sno> 0 and > amount> 0 group by sno,branch,edition having count(distinct userid) > 1 ; > count > ------- > (0 rows) > > tradein_clients=# > tradein_clients=# > tradein_clients=# SELECT count(*) from public.users where userid=-1; > count > ------- > 0 > (1 row) > > tradein_clients=# It's because of your having clause. If count(*)=0 then having count(*)>1 results in no rows. Regards, Tomasz Myrta
Tomasz did you notice the two queries are totally differet on different tables? On Thursday 06 March 2003 05:30 pm, Tomasz Myrta wrote: > Rajesh Kumar Mallah wrote: > > When does count(*) returns "o rows" ? > > and when does it return 1 row value being 0. > > > > tradein_clients=# SELECT count(*) from public.eyp_listing where sno> 0 > > and amount> 0 group by sno,branch,edition having count(distinct userid) > > > 1 ; count > > ------- > > (0 rows) > > > > tradein_clients=# > > tradein_clients=# > > tradein_clients=# SELECT count(*) from public.users where userid=-1; > > count > > ------- > > 0 > > (1 row) > > > > tradein_clients=# > > It's because of your having clause. > If count(*)=0 then having count(*)>1 results in no rows. > > Regards, > Tomasz Myrta -- Regds Mallah ---------------------------------------- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
On Fri, 7 Mar 2003, Rajesh Kumar Mallah wrote: > Tomasz did you notice the two queries are totally differet on different > tables? A count() on a non-grouped table returns 1 row. A grouped table returns 1 row per group. If there are no groups, no rows are returned. If the groups are filtered by a having clause, it might also return no rows.
On Friday 07 March 2003 11:41 am, Stephan Szabo wrote: > On Fri, 7 Mar 2003, Rajesh Kumar Mallah wrote: > > Tomasz did you notice the two queries are totally differet on different > > tables? > > A count() on a non-grouped table returns 1 row. > > A grouped table returns 1 row per group. If there are no groups, no rows > are returned. If the groups are filtered by a having clause, it might > also return no rows. True the second query does not return any row. even in first case no rows are returned. where is the difference? tradein_clients=# select userid from users where userid=-1; +--------+ | userid | +--------+ +--------+ (0 rows) Time: 846.86 ms tradein_clients=# Regds Mallah ---------------------------------------- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
On Fri, 7 Mar 2003, Rajesh Kumar Mallah wrote: > On Friday 07 March 2003 11:41 am, Stephan Szabo wrote: > > On Fri, 7 Mar 2003, Rajesh Kumar Mallah wrote: > > > Tomasz did you notice the two queries are totally differet on different > > > tables? > > > > A count() on a non-grouped table returns 1 row. > > > > A grouped table returns 1 row per group. If there are no groups, no rows > > are returned. If the groups are filtered by a having clause, it might > > also return no rows. > > True the second query does not return any row. > even in first case no rows are returned. > > where is the difference? > I don't think I understand the question. select userid from users where userid=-1 returns 1 row for each row the where clause returns true and 0 rows if there are none. select count(*) from users where userid=-1 returns 1 row (it's not a grouped table) select count(*) from users where userid=-1 group by userid returns 1 row if there is at least 1 row having userid=-1or 0 rows otherwise (either there is a group or there isn't) select count(*) from users where userid=-1 group by useridhaving userid!=-1 should return 0 rows because even if there is a group it'll fail the having clause.
i will think more abt this issue and will discuss. regds mallah. On Friday 07 March 2003 12:10 pm, Stephan Szabo wrote: > On Fri, 7 Mar 2003, Rajesh Kumar Mallah wrote: > > On Friday 07 March 2003 11:41 am, Stephan Szabo wrote: > > > On Fri, 7 Mar 2003, Rajesh Kumar Mallah wrote: > > > > Tomasz did you notice the two queries are totally differet on > > > > different tables? > > > > > > A count() on a non-grouped table returns 1 row. > > > > > > A grouped table returns 1 row per group. If there are no groups, no > > > rows are returned. If the groups are filtered by a having clause, it > > > might also return no rows. > > > > True the second query does not return any row. > > even in first case no rows are returned. > > > > where is the difference? > > I don't think I understand the question. > > select userid from users where userid=-1 > returns 1 row for each row the where clause returns true > and 0 rows if there are none. > > select count(*) from users where userid=-1 > returns 1 row (it's not a grouped table) > > select count(*) from users where userid=-1 group by userid > returns 1 row if there is at least 1 row having userid=-1 > or 0 rows otherwise (either there is a group or there isn't) > > select count(*) from users where userid=-1 group by userid > having userid!=-1 > should return 0 rows because even if there is a group it'll fail the > having clause. > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Regds Mallah ---------------------------------------- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.