Обсуждение: count(1) return 0?

Поиск
Список
Период
Сортировка

count(1) return 0?

От
Zak McGregor
Дата:
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
========================================================================

Re: count(1) return 0?

От
Bill Moran
Дата:
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


Re: count(1) return 0?

От
Zak McGregor
Дата:
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
========================================================================

Re: count(1) return 0?

От
Bill Moran
Дата:
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


Re: count(1) return 0?

От
Klint Gore
Дата:
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             :                 :
+---------------------------------------+-----------------+

Re: count(1) return 0?

От
Kris Jurka
Дата:

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



Re: count(1) return 0?

От
Zak McGregor
Дата:
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
========================================================================

Re: count(1) return 0?

От
"Uwe C. Schroeder"
Дата:
-----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-----


Re: count(1) return 0?

От
Klint Gore
Дата:
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             :                 :
+---------------------------------------+-----------------+

Re: count(1) return 0?

От
Zak McGregor
Дата:
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
========================================================================

Re: count(1) return 0?

От
Greg Stark
Дата:
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