SOLVED: Need Help With This Count() Query

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

General SQL Server Forums

New to SQL Server Programming

SOLVED: Need Help With This Count() Query

Author

Topic

MrQuestions
Starting Member

7 Posts

Posted - 2008-03-23 : 16:41:01

Hello,uery - I'm Stuck

I'm very rusty with my SQL, and could use a little assistance on building this query. Thanks for taking the time to help me. It should be a relatively simple Count() query but I'm not getting the right results for some reason and I'm hoping somebody can point out my error to me.

I'm attempting to count the number of times each m_id is returned after running this query:

SELECT m_id
FROM taglink
WHERE m_id <> '25'
AND t_id
IN (
SELECT t_id
FROM taglink
WHERE m_id = '25'
)

which returns:
m_id
33
34
34
35
35
35
36

I want to count the number of times that m_id is returned so that the results of my query will be:

m_id | count
33 | 1
34 | 2
35 | 3
36 | 1

In my attempt to do this, I run the following query:

SELECT m_id, count( m_id ) AS "count"
FROM taglink
WHERE m_id
IN (
SELECT m_id
FROM taglink
WHERE m_id <> '25'
AND t_id
IN (
SELECT t_id
FROM taglink
WHERE m_id = '25'
)
)
GROUP BY m_id

The problem I'm having is that the query returns:

m_id | count
33 | 3
34 | 2
35 | 3
36 | 3

It seems to return the count of "3" for fields that should count "1," but count correctly if the fields count "2" or "3."

Perhaps somebody can spot my error... I can't seem to wrap my brain around this one. Thanks so much for your time!

MrQuestions
Starting Member

7 Posts

Posted - 2008-03-23 : 16:55:07

Ahh, I think I might have figured it out... but not entirely... It may be because I'm selecting from the taglink table and not from the subquerys... how to select it directly from the subquery...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-23 : 18:57:38

It's unclear from your post exactly what you are trying to do. Are you trying to find the M_ids that have t_ids

SELECT a.m_id,[count] = count(*)
FROM
(SELECT m_id, count( m_id ) AS "count"
FROM taglink
WHERE m_id
IN (
SELECT m_id
FROM taglink
WHERE m_id <> '25'
AND t_id
IN (
SELECT t_id
FROM taglink
WHERE m_id = '25'
)
)
GROUP BY m_id ) a
GROUP BY a.m_id

But if you could state more clearly what you are after, this could probably be written more clearly. For example : "I want all the m_ids where the t_ids have an m_id = 25 and at leat one m_id <> 25"
Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-23 : 19:13:59

SELECT m_id,COUNT(m_id)
FROM taglink
WHERE m_id <> '25'
AND t_id
IN (
SELECT t_id
FROM taglink
WHERE m_id = '25'
)
GROUP BY m_id

MrQuestions
Starting Member

7 Posts

Posted - 2008-03-24 : 15:19:13

Ah hah! I figured out how to do it. I failed to mention that I'm using MySQL and needed to declare an alias when creating a temporary subquery table. Thank you for your replies, I appreciate it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources