473,287 Members | 1,827 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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

Nov 23 '05 #1
10 3704
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

Nov 23 '05 #2
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

Nov 23 '05 #3
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

Nov 23 '05 #4
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 : 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 : :
+---------------------------------------+-----------------+

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

Nov 23 '05 #5


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

Nov 23 '05 #6
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 <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

Nov 23 '05 #7
-----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: +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+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

Nov 23 '05 #8
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 : 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 : :
+---------------------------------------+-----------------+

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

Nov 23 '05 #9
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
================================================== ======================

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

Nov 23 '05 #10
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

Nov 23 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

22
by: Ling Lee | last post by:
Hi all. I'm trying to write a program that: 1) Ask me what file I want to count number of lines in, and then counts the lines and writes the answear out. 2) I made the first part like this: ...
1
by: JD | last post by:
Hi guys I'm trying to write a program that counts the occurrences of HTML tags in a text file. This is what I have so far: #include <stdio.h> #include <stdlib.h> #include <string.h> ...
1
by: Greg Smith | last post by:
I am trying to write a SQL query that will return a record count to my C# application. I wrote the following query: ALTER PROCEDURE up_justification_duplicate AS SELECT COUNT(*) FROM...
10
by: Jon | last post by:
I want to count the number of instances of a certain string(delimiter) in another string. I didn't see a function to do this in the framework (if there is, please point me to it). If not, could...
68
by: Martin Joergensen | last post by:
Hi, I have some files which has the following content: 0 0 0 0 0 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 0 0 0 0 0
2
by: GoCoogs | last post by:
I'm trying to count how many items are in a dynamic collection. This is the code I have so far. *** Begin Code *** Public Class Rule Private _rulevars As RuleVarsCollection Private _rulename...
23
by: Gary Wessle | last post by:
Hi I have a vector<charwhich looks like this (a d d d a d s g e d d d d d k) I need to get the biggest count of consecutive 'd'. 5 in this example I am toying with this method but not sure if...
26
by: Ping | last post by:
Hi, I'm wondering if it is useful to extend the count() method of a list to accept a callable object? What it does should be quite intuitive: count the number of items that the callable returns...
3
by: cmartin1986 | last post by:
I have written a sql query and I need it to return 0 when it doesn't find any matches to my criteria. I have tried adding iif statements, tried sum, and just Count, all of these methods work fine to...
12
by: subramanian100in | last post by:
Below is my understanding about count algorithms. Return type of count and count_if algorithms is iterator_traits<InputIterator>::difference_type. If the container contains more than...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.