| Author |
Topic |
|
Jusvistin
Yak Posting Veteran
81 Posts |
 Posted - 2003-08-14 : 04:41:28
|
Message Preview I'm doing a left outer join on two tables, and although the right table contains no rows for some Party Codes - the query returns a count of 1 instead of zero.Table 1 (party)Code, Name, Leader Table 2 (msp)Name, Party, Constituency SELECT p.code, count(*)FROM party p left join msp m on (p.code = m.party)GROUP BY p.code If I change count(*) to count(m.party) it works fine. Thanks,Kevin |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-08-14 : 04:46:55
|
So what's your question  That is the expected behaviour surely?-------Moo. :) |
 |
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-14 : 05:18:58
|
Well, the behavior wasn't exptected to me  , until now.I guess my question was, is this what I should expect ?  Actually the question would have been, why does count(*) return a count of one, but I guess I didn't understand what it was counting.I think I get it now (well sort of).Kevin |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-08-14 : 05:27:36
|
| Count(*) will count any field in the row, and your row contains the left side of the join (so one row) at least. If you get any joins for the right hand side then the grouped by count will increase to show the right hand side figures if there's more than one. If you count(a specific field on the right hand side) you'll only get a value for where that figure is not null.-------Moo. :) |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-08-14 : 09:28:06
|
quote:
why does count(*) return a count of one
quote:
If I change count(*) to count(m.party) it works fine.
If Count(*) returns 1, what does count(m.party) return? Zero? It can't be greater than count(*) [which is 1].Sam |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-08-14 : 09:34:56
|
quote:
Originally posted by SamCIf Count(*) returns 1, what does count(m.party) return? Zero? It can't be greater than count(*) [which is 1].Sam
With politicians and parties, you can never tell...Owais |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2003-08-14 : 09:43:37
|
| aggregates operate on non-NULL values with one exception COUNT(*) which is a count of rows returned in which there is at lease 1 non-NULL value across all fields in the result set. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-08-14 : 09:55:33
|
quote:
aggregates operate on non-NULL values with one exception COUNT(*) which is a count of rows returned in which there is at lease 1 non-NULL value across all fields in the result set.
I don't understand Kevin's problem. He says count(m.party) works fine and implied count(*) [which returns 1] doesn't work fine.I guess it's possible that zero was the value he was looking for. Seems like such a small number though..Sam |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-08-14 : 10:11:07
|
| I think he possibly was under the impression that count(*) would only return 1 if there was a match on the right hand side of the join.-------Moo. :) |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-08-14 : 10:16:57
|
quote:
Originally posted by drymchaser
aggregates operate on non-NULL values with one exception COUNT(*) which is a count of rows returned in which there is at lease 1 non-NULL value across all fields in the result set.
Pardon? COUNT(*) doesn't depend on the values in any columns. |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2003-08-14 : 10:23:00
|
| I suppose I over-complicated my statement which is to say count(*) returns the count of rows in the result set |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-08-14 : 10:35:49
|
| I am reminded of the joke that starts out:How many engineers does it take to screw in a lightbulb?Sam |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-08-14 : 10:45:17
|
| I don't know, How many engineers does it take to screw in a lightbulb?-------Moo. :) |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-08-14 : 10:48:03
|
| I don't know, maybe they screw in the dark ... Damian |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2003-08-14 : 10:57:56
|
| None, engineers don't DO any work... |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-08-14 : 11:58:26
|
| LOL!!Q. How many Microsoft Engineers does it take to screw in a light bulb?A. None, Microsoft simply makes darkness the new world standard!wais |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2003-08-14 : 15:54:17
|
quote:
Originally posted by mohdowais
LOL!!Q. How many Microsoft Engineers does it take to screw in a light bulb?A. None, Microsoft simply makes darkness the new world standard!wais
Good one mohdowais.Of course it was Probably one of those who made this new worm. Nothing is more destructive than a intelligent and disgruntled Ex-employee. JimUsers <> Logic |
|
|
|
|
Left outer join + COUNT(*) question