Having problems with using COUNT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Having problems with using COUNT

Author

Topic

rett
Starting Member

35 Posts

Posted - 2007-12-14 : 18:21:34

Here we go.

I have categories and Name ID's. Within a category I would like to know the total number of Name ID's for each category

example:

Accumula 105294
Aerosols 105058
Air Cond 105294
Air Star 105058
Bottomho 109967
Cement-P 109967
Consulta 211252
Drill Co 211252
Safety-E 105294

Here my statement

SELECT DISTINCT
L.LAST_CATEGORY_DESC, COUNT(L.NAME_ID) AS Expr1
FROM dbo.Table AS L INNER JOIN
dbo.CatTable AS C ON L.PUB_ID = C.PUB_ID AND L.LAST_CATEGORY_LVL_ID = C.LAST_CATEGORY_LVL_ID
WHERE (C.WEB_ACTIVATED_IND = 1) AND
GROUP BY L.LAST_CATEGORY_DESC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-15 : 10:03:21

1) Why do you have DISTINCT in your SELECT? That should not be there, there is no reason for it. Be sure that you completely understand how DISTINCT works and how GROUP BY works.

2) What is your question or problem?

- Jeff
http://weblogs.sqlteam.com/JeffS

rett
Starting Member

35 Posts

Posted - 2007-12-15 : 11:44:56

Hi Jeff,

Here's my problem. In my table I have categories that can appear multiple times. So I want to only show the category description 1 time but also get the total number of Name ID's that are under that 1 category.

By using the DISTINCT it is giving me the 1 category but the COUNT is not working for the total number of Name ID's

rett

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-15 : 12:04:59

When you GROUP BY category It will not appear multiple times in the output. So take DISTINCT out of select statement to get distinct category names and count of associated name ids.
Alternatively if you are having repeated Category and Name id groups then take DISTINCT of these and then group by Category and take count.

rett
Starting Member

35 Posts

Posted - 2007-12-15 : 12:33:51

Okay,

Remove the DISTINCT and using the GROUP BY is working. But when I use the COUNT(L.NAME_ID). I kept getting the number 2 for each.

For example: I should be getting 3 Name ID's for category "Consulta" but I get the number 2 for all categories.

Accumula 105294

Aerosols 105058

Air Cond 105294
105292

Air Star 105058

Consulta 211252
109967
211252

Drill Co 211252

Safety-E 105294

cardgunner

326 Posts

Posted - 2007-12-15 : 13:24:55

rett if I may ask to understand. From the info on the first post the result you are looking for is

3 105294
2 105058
2 109967
2 211252

??

Card Gunner

rett
Starting Member

35 Posts

Posted - 2007-12-15 : 13:33:47

hi cardgunner,

The results that I am looking for is:

Total number of Name ID's for category "Consulta" 3
Total number of Name ID's for category "Air Cond" 2
Total number of Name ID's for category "Drill Co" 1

etc...

I just want the COUNT of the Name ID's but not display the Name ID's

Thanks

cardgunner

326 Posts

Posted - 2007-12-15 : 13:44:20

Given your example on the first post that is not accurate.

Accumula 105294
Aerosols 105058
Air Cond 105294
Air Star 105058
Bottomho 109967
Cement-P 109967
Consulta 211252
Drill Co 211252
Safety-E 105294

Card Gunner

rett
Starting Member

35 Posts

Posted - 2007-12-15 : 14:10:16

hi cardgunner,

From my first example each category should only have a total number of 1:

Accumula 1 Name Id (105294) for this category
Aerosols 1 Name Id (105058) for this category
Air Cond 1 Name Id (105294) for this category
Air Star 1 Name Id (105058) for this category
Bottomho 1 Name Id (109967) for this category
Cement-P 1 Name Id (109967) for this category
Consulta 1 Name Id (211252) for this category
Drill Co 1 Name Id (211252) for this category
Safety-E 1 Name Id (105294) for this category

cardgunner

326 Posts

Posted - 2007-12-15 : 14:21:26

rett,

from the results from post 7

Total number of Name ID's for category "Consulta" 3
Total number of Name ID's for category "Air Cond" 2
Total number of Name ID's for category "Drill Co" 1

would be in your underlying table or query without the count function as

consulta 211252
air cond 105294
consulta 111111
drill co 211252
consulta 222222
air cond 333333

where consulta is in your table with 3 different id's and so and so on.

If that is correct, if it was in there twice with the same ID should we count it as 2 or 1? I would guess 1 but..

Card Gunner

rett
Starting Member

35 Posts

Posted - 2007-12-15 : 15:19:07

hi cardgunner,

yes it is possible to have the same Name ID more then 1 time.

so the answer would be 2 not 1.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources