Different Record Counts - 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

Different Record Counts

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-04-12 : 15:52:38

Does any one have an idea on why these record counts are different? I would expect them to be the same.

The oehdrhst is a header table. The oelinhst holds line detail. They are linked by ord_no and inv_no.

Header will have one record with a unique inv_no and the line can have many records with the same inv_no


select distinct(count(oelinhst_sql.ord_no))
from oelinhst_sql join oehdrhst_sql on oelinhst_sql.ord_no = oehdrhst_sql.ord_no and
oelinhst_sql.inv_no = oehdrhst_sql.inv_no
where oehdrhst_sql.ord_dt > 20110331 
COUNT = 185 (INCORRECT)

select distinct(count(ord_no))
from oehdrhst_sql
where oehdrhst_sql.ord_dt > 20110331
COUNT = 132 (CORRECT)

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-04-12 : 15:59:02

When I do this Query I get 132 records returned???

select distinct oelinhst_sql.inv_no
from oelinhst_sql join oehdrhst_sql on oelinhst_sql.ord_no = oehdrhst_sql.ord_no and
oelinhst_sql.inv_no = oehdrhst_sql.inv_no
where oehdrhst_sql.ord_dt > 20110331
order by oelinhst_sql.inv_no desc

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-04-12 : 16:26:32

Seems to be when I include multiple fields when I'm selecting from the oelinhst when the record count jumps. If I only select one field line inv_no or ord_no I get the 132 record Count.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-13 : 01:28:32

try this: select count(distinct oelinhst_sql.ord_no)

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources