| Author |
Topic |
|
AMB
Starting Member
6 Posts |
 Posted - 2006-10-30 : 05:58:34
|
| I have a table that contains customer names (CUSTNAME) I want to be able to count the number of customers when querying the table. The count must only count each customer once (as they can appear multiple times) - Can anyone help?Thanks, AMB |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-30 : 06:01:02
|
| select count(*) cnt from (select distinct custname from mytable) xPeter LarssonHelsingborg, Sweden |
 |
|
|
rameshuddaraju
Starting Member
1 Post |
Posted - 2006-10-30 : 07:41:08
|
| select count(distinct custname) from yourtablename |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-10-30 : 14:00:22
|
and just for completness:SELECT COUNT(*) FROM (SELECT CustName FROM MyTable GROUP BY CustName) D (why so complicate Peter, is there some hidden benefit I am not aware of?)-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter." |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-30 : 14:05:36
|
| I am not sure COUNT(distinct ..) is valid on all DBMS. I thought if he was using T-SQL, he would already know about COUNT.I think your query will produce a list of 1, 1, 2, 1, 1 and so on...Peter LarssonHelsingborg, Sweden |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-10-30 : 14:58:59
|
| Sigh, you are (of course) absolutly right, it is fixed now though ... just one of them days.Ill add an observation though, all three gives the same execution plan (at least on 2005)-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter." |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-30 : 15:04:08
|
And now Paul, what is the difference between your query and mine, besides a GROUP BY or DISTINCT?  Peter LarssonHelsingborg, Sweden |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-10-30 : 15:10:08
|
| My point wasn't that yours were unnessesery complex, but that you didn't used COUNT(DISTINCT ..), I just thought we should have a GROUP BY solution as well to complete the options And my question if it was on purpose was genuine, you rarely do anything (contrary to me) out of ignorence.-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter." |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-30 : 15:25:04
|
Whats needed here is yet another variation.select sum(one) from (select one=1 from MyTable group by CustName ) a CODO ERGO SUM |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-10-30 : 15:43:15
|
LOL, trueInteresting enough, that one doesn't always performe equally well, it gets a slightly different execution plan (on both 2000 and 2005), but I can only get it to run (a tiny bit) slower on a 2000 server. slower on 2000 than 2005 (not much on either though).-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter." |
|
|
|
|
Help with COUNT