Problems with a COUNT query - SQL Server Forums

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

Problems with a COUNT query

Author

Topic

MGarriga
Starting Member

5 Posts

Posted - 2009-08-07 : 14:12:35

Hello,

If I run the following query:

select major,minor,COUNT(*)numaccy
from EXTR_Accessory
group by Major,Minor
order by numaccy,Major,Minor

I get 178710 rows.

If instead I run the following query:

SELECT a.Major, a.Minor
FROM EXTR_Accessory a
INNER JOIN EXTR_RPSale b
ON a.Major = b.Major
AND a.Minor = b.Minor
GROUP BY a.Major, a.Minor
ORDER BY a.Major,a.Minor

I get 154546 rows.

What I am trying to do, is the same query above, but including a column with the Count of (Major+Minor), how they appear in the first query, so I tried the following query, with a result of 0 rows, when I know I should get 154546 rows:

SELECT b.Major,b.Minor,a.NumAccy
FROM (SELECT Major,minor, COUNT (*) as NumAccy
FROM EXTR_Accessory
GROUP BY Major,Minor) a
INNER JOIN EXTR_RPSale b
ON a.Major = b.Major
AND b.Major = b.Minor
ORDER BY a.NumAccy

What I am doing wrong?

Thank you,
Montse

johnconstraint
Starting Member

23 Posts

Posted - 2009-08-07 : 14:56:41

Should your AND condition in your third query be like below ?

AND a.minor = b.Minor

MGarriga
Starting Member

5 Posts

Posted - 2009-08-07 : 19:32:42

Thank you.
I needed a break ;o)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources