Why Am I Not Getting The Connect 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)

Why Am I Not Getting The Connect COUNT

Author

Topic

rett
Starting Member

35 Posts

Posted - 2007-12-16 : 17:19:27

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:

Here my statement

SELECT L.LAST_CATEGORY_DESC, COUNT(L.NAME_ID) AS Expr1
FROM dbo.Table
GROUP BY L.LAST_CATEGORY_DESC

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

Air Cond 105294
105292
Consulta 211252
109967
211252
Drill Co 211252

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

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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-16 : 17:33:27

YOu are counting the L.Name_ID. You are only getting 2 because there are only 2 unique Name ID's (1 for 109967, and 2 for 211252. But only 2 unique name ID's)

SELECT L.LAST_CATEGORY_DESC, COUNT(*) AS Expr1
FROM dbo.Table
GROUP BY L.LAST_CATEGORY_DESC

Poor planning on your part does not constitute an emergency on my part.

rett
Starting Member

35 Posts

Posted - 2007-12-16 : 19:00:25

hi dataguru1971,

thanks for your response. i tried changing the statement to this

SELECT L.LAST_CATEGORY_DESC, COUNT(*) AS Expr1
FROM dbo.Table
GROUP BY L.LAST_CATEGORY_DESC

and i am still getting a total of 2 for each category.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-16 : 19:31:20

Strange. If there are 3 records with that descriptions, the count of 3 should be returned. Obviously, this is a truncated version of your actual query....what else is in there that isn't obvious from the post? Is there a join or anything that might restrict the rows returned?

Poor planning on your part does not constitute an emergency on my part.

rett
Starting Member

35 Posts

Posted - 2007-12-16 : 19:56:28

here the full statement

SELECT L.LAST_CATEGORY_DESC, COUNT(*) AS Expr1
FROM dbo.DATATABLES AS L INNER JOIN
dbo.CATEGORIES 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 (C.CATEGORY_STATUS_ID = 'A') AND (L.DETAIL_LIST_OUTPUT_ID = 'B' OR
L.DETAIL_LIST_OUTPUT_ID = 'W') AND L.LANGUAGE_ID = 'ENG'
GROUP BY L.LAST_CATEGORY_DESC

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-16 : 20:04:34

See...that changes everything. We can't help you solve problems with only part of the picture.

Try changing INNER to LEFT and see if the results change.

Poor planning on your part does not constitute an emergency on my part.

rett
Starting Member

35 Posts

Posted - 2007-12-16 : 20:22:24

still getting the same results

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-16 : 20:34:28

[code]
SELECT L.LAST_CATEGORY_DESC, COUNT(L.*) AS Expr1
FROM dbo.DATATABLES L
LEFT JOIN dbo.CATEGORIES 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 (C.CATEGORY_STATUS_ID = 'A')
AND (L.DETAIL_LIST_OUTPUT_ID = 'B' OR L.DETAIL_LIST_OUTPUT_ID = 'W')
AND L.LANGUAGE_ID = 'ENG'
GROUP BY L.LAST_CATEGORY_DESC
[/code]

I added L.* instead of just * given the join.

Presuming that is what your query looks like now, you need to check the filters in your WHERE clause..

Safe to say now that you are getting incorrect results because you are filtering records out of the data.

Poor planning on your part does not constitute an emergency on my part.

rett
Starting Member

35 Posts

Posted - 2007-12-16 : 20:54:13

thanks dataguru1971 for all your help today. you found the problem.

i need to add another part to my FROM clause

the statement need to look like this:

SELECT L.LAST_CATEGORY_DESC, COUNT(*) AS Expr1
FROM dbo.DATATABLES L
LEFT JOIN dbo.CATEGORIES C ON L.PUB_ID = C.PUB_ID
AND L.PUB_SECTION_PUB_ID = C.PUB_SECTION_ID AND L.LAST_CATEGORY_LVL_ID = C.LAST_CATEGORY_LVL_ID

WHERE (C.WEB_ACTIVATED_IND = 1)
AND (C.CATEGORY_STATUS_ID = 'A')
AND (L.DETAIL_LIST_OUTPUT_ID = 'B' OR L.DETAIL_LIST_OUTPUT_ID = 'W')
AND L.LANGUAGE_ID = 'ENG'
GROUP BY L.LAST_CATEGORY_DESC

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-16 : 21:04:41

Cool.

Remember that simplifying your statement for the purposes of posing the question, as in this case, did nothing to help your cause. When posting here, more specific the better....if you had posted the larger query first, it would have been a quicker solve.

Good luck, and your welcome.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources