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
================================================== ======================

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Zak McGregor]

On Sun, 29 Feb 2004 19:55:15 -0500
Bill Moran <wm****@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

[Bill Moran]

Zak McGregor wrote:

On Sun, 29 Feb 2004 19:55:15 -0500
Bill Moran <wm****@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Klint Gore]

On Mon, 1 Mar 2004 02:14:56 +0200, Zak McGregor <za*@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 : [e-mail] : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : [phone] : :
+---------------------------------------+-----------------+

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Zak McGregor]

On Mon, 01 Mar 2004 12:13:07 +1100
Klint Gore <[e-mail]> wrote:

On Mon, 1 Mar 2004 02:14:56 +0200, Zak McGregor <za*@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
================================================== ======================

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[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 <wm****@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: [phone] San Bruno, CA 94066
Cell: [phone] United States
Fax: [phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAQpLXjqGXBvRToM4RAh1CAJ4knBgzklnG+Dmuy+62LN AU3JGqDwCcCxUY
w1VAIOauzfpO49QgK42et4c=
=/U+x
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Klint Gore]

On Mon, 1 Mar 2004 03:27:39 +0200, Zak McGregor <za*@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 : [e-mail] : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : [phone] : :
+---------------------------------------+-----------------+

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Zak McGregor]

On Mon, 01 Mar 2004 13:12:12 +1100
Klint Gore <[e-mail]> 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
================================================== ======================

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Greg Stark]

Zak McGregor <za*@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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster