Thread: count(1) return 0?
Hi all is it possible to get a count(1) statemment, for example here: select count(1) from results where fixture=4916 and winner=away group by winner; to return a 0 value instead of absolutely nothing if no rows match fixture=4916 and winner=away? I get absolutely no results at all. select count(1) from results where fixture=4916 and winner=home group by winner; count ------- (0 rows) any ideas please? Thanks ciao Zak -- ======================================================================== http://www.carfolio.com/ Searchable database of 10 000+ car specs ========================================================================
Zak McGregor wrote: > Hi all > > is it possible to get a count(1) statemment, for example here: > > select count(1) from results where fixture=4916 and winner=away group by winner; > > to return a 0 value instead of absolutely nothing if no rows match fixture=4916 > and winner=away? I get absolutely no results at all. > > > select count(1) from results where fixture=4916 and winner=home group by winner; > count > ------- > (0 rows) > > any ideas please? Not really sure I understand what you want, but try something like this: select case when count(*) > 0 then ''t'' else ''f'' end from ... -- Bill Moran Potential Technologies http://www.potentialtech.com
On Sun, 29 Feb 2004 19:55:15 -0500 Bill Moran <wmoran@potentialtech.com> wrote: > Not really sure I understand what you want, but try something like this: > > select case when count(*) > 0 then ''t'' else ''f'' end from ... Unfortunately that does not work. When the resultset contains no records whatsoever, I get no rows at all - so on case...else sort of expression within the select itself will work. What I am looking for is a way to select "0" if no rows are returned, or the total number of rows returned from the query otherwise. Thanks anyways though. Ciao Zak -- ======================================================================== http://www.carfolio.com/ Searchable database of 10 000+ car specs ========================================================================
Zak McGregor wrote: > On Sun, 29 Feb 2004 19:55:15 -0500 > Bill Moran <wmoran@potentialtech.com> wrote: > >>Not really sure I understand what you want, but try something like this: >> >>select case when count(*) > 0 then ''t'' else ''f'' end from ... > > Unfortunately that does not work. When the resultset contains no records > whatsoever, I get no rows at all - so on case...else sort of expression within > the select itself will work. > > What I am looking for is a way to select "0" if no rows are returned, or the > total number of rows returned from the query otherwise. I suspect that I still don't understand what you're trying to do, but ... select case when count(*) > 0 then 1 else 0 end from ... Was what I should have suggested. -- Bill Moran Potential Technologies http://www.potentialtech.com
On Mon, 1 Mar 2004 02:14:56 +0200, Zak McGregor <zak@mighty.co.za> wrote: > to return a 0 value instead of absolutely nothing if no rows match fixture=4916 > and winner=away? I get absolutely no results at all. > > any ideas please? dont group by winner. it's not returned in the statement so it's not needed anyway. an exact value is specified in the where clause so it's not going to be different either. klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
On Mon, 1 Mar 2004, Zak McGregor wrote: > Hi all > > is it possible to get a count(1) statemment, for example here: > > select count(1) from results where fixture=4916 and winner=away group > by winner; > > to return a 0 value instead of absolutely nothing if no rows match > fixture=4916 and winner=away? I get absolutely no results at all. > Your problem is the GROUP BY. If you take that off it should work as expected. Kris Jurka
On Mon, 01 Mar 2004 12:13:07 +1100
Klint Gore <kg@kgb.une.edu.au> wrote:
> On Mon, 1 Mar 2004 02:14:56 +0200, Zak McGregor <zak@mighty.co.za> wrote:
> > to return a 0 value instead of absolutely nothing if no rows match
> > fixture=4916 and winner=away? I get absolutely no results at all.
> >
> > any ideas please?
>
> dont group by winner. it's not returned in the statement so it's not
> needed anyway. an exact value is specified in the where clause so it's
> not going to be different either.
Thanks Klint, that works. I will need to group by fixture though (not winner -
that was just one of the permutations that I was playing around with) at some
point which presents the same problem. I have a number of fixtures in the
results table which need to be aggregated to tally frames won per player per
fixture (stored as a view) and those results further aggregated to find points
for a points table. All is good until a player has won no frames in a fixture,
at which point the fact that an empty result is returned becomes problematic.
For instance:
select fixture, count(1) as total, away from results where winner=away group by
fixture, away;
fixture | total | away
---------+-------+------
4913 | 4 | 1214
4916 | 9 | 1200
4918 | 7 | 1123
4928 | 9 | 1318
4935 | 5 | 1265
(5 rows)
select fixture, count(1) as total, home from results where winner=home group by
fixture, home;
fixture | total | home
---------+-------+------
4913 | 9 | 1198
4918 | 9 | 1257
4928 | 1 | 1401
4935 | 9 | 1359
(4 rows)
in fixture 4916, player 1200 won 9-0, but his opponent does not show as having 0
in the second result set. I suspect what I am expecting is somehow illogical,
but I can't see why.
I would expect to see a total of 0 for the case where winner=away and
fixture=4916 = the fixtures are after all being grouped - if there are zero
cases where fixture=4916 and winner=away, as far as I can see that should be the
count() result... however as I said I am probably missing something quite basic.
If anyone could please explain why what I expect to see is not what I actually
see I would be very grateful indeed.
Thanks
Ciao
Zak
--
========================================================================
http://www.carfolio.com/ Searchable database of 10 000+ car specs
========================================================================
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 What is it you want ? the count of rows matching the criteria ? If so a standard count(*) should do. If the resultset has no rows the count(*) will return 0 as result. On Sunday 29 February 2004 05:02 pm, Zak McGregor wrote: > On Sun, 29 Feb 2004 19:55:15 -0500 > > Bill Moran <wmoran@potentialtech.com> wrote: > > Not really sure I understand what you want, but try something like this: > > > > select case when count(*) > 0 then ''t'' else ''f'' end from ... > > Unfortunately that does not work. When the resultset contains no records > whatsoever, I get no rows at all - so on case...else sort of expression > within the select itself will work. > > What I am looking for is a way to select "0" if no rows are returned, or > the total number of rows returned from the query otherwise. > > Thanks anyways though. > > Ciao > > Zak > > -- > ======================================================================== > http://www.carfolio.com/ Searchable database of 10 000+ car specs > ======================================================================== > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAQpLXjqGXBvRToM4RAh1CAJ4knBgzklnG+Dmuy+62LNAU3JGqDwCcCxUY w1VAIOauzfpO49QgK42et4c= =/U+x -----END PGP SIGNATURE-----
On Mon, 1 Mar 2004 03:27:39 +0200, Zak McGregor <zak@mighty.co.za> wrote: > in fixture 4916, player 1200 won 9-0, but his opponent does not show as having 0 > in the second result set. I suspect what I am expecting is somehow illogical, > but I can't see why. > > I would expect to see a total of 0 for the case where winner=away and > fixture=4916 = the fixtures are after all being grouped - if there are zero > cases where fixture=4916 and winner=away, as far as I can see that should be the > count() result... however as I said I am probably missing something quite basic. > If anyone could please explain why what I expect to see is not what I actually > see I would be very grateful indeed. The counting and grouping is done after the where clause is applied. since player iplaybadly (who was 1200's opponent) didnt win any, he/she is not included in the result set to be grouped and counted. You need to get iplaybadly into the result set first. try something like select fixture, home, sum(case winner=home then 1 else 0 end) >from results group by fixture, home klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
On Mon, 01 Mar 2004 13:12:12 +1100 Klint Gore <kg@kgb.une.edu.au> wrote: > The counting and grouping is done after the where clause is applied. > > since player iplaybadly (who was 1200's opponent) didnt win any, he/she > is not included in the result set to be grouped and counted. You need > to get iplaybadly into the result set first. > > try something like > > select fixture, home, sum(case winner=home then 1 else 0 end) > >from results > group by fixture, home Ah, thanks, works a charm. Thanks too to all who offered help on this one. Ciao Zak -- ======================================================================== http://www.carfolio.com/ Searchable database of 10 000+ car specs ========================================================================
Zak McGregor <zak@mighty.co.za> writes: > in fixture 4916, player 1200 won 9-0, but his opponent does not show as having 0 > in the second result set. I suspect what I am expecting is somehow illogical, > but I can't see why. It's illogical because the database would have no idea what fixture groups to include if they don't appear in the results table. It doesn't know what the range of possible values are. What you would have to do is do an outer join against an exhaustive list of possible fixture values and then do a count(results.fixture) or something like that. So for example something like: SELECT count(results.fixture) AS home_wins, home FROM fixtures LEFT OUTER JOIN results USING (fixture) WHERE winner=home GROUP BY fixture,home -- greg