Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

SQL server count error!!!

I have two tables.

Table1 (master) :
fields:
ID Numeric (unique)
Type Numeric

Table2 (transactions):
fields:
ID Numeric
CrOnDate datetime

Nb: all trasaction are in the month 3 and 4 (march and april)

When I run following queries A, B1, B2. all counts from A should be equal to all counts from B1 + all counts from B2. But I get different counts!!! Do you know why?

A.
SELECT TYPE, COUNT(*) AS ROWSCOUNTS FROM TABLE1  WHERE ID IN (SELECT ID FROM TABLE2 GROUP BY ID) AND TYPE = 2 GROUP BY TYPE

B1.
SELECT TYPE, COUNT(*) AS ROWSCOUNTS FROM TABLE1  WHERE ID IN (SELECT ID FROM TABLE2 WHERE MONTH(CrOnDate) = 3 GROUP BY ID) AND TYPE = 2 GROUP BY TYPE

B2.
SELECT TYPE, COUNT(*) AS ROWSCOUNTS FROM TABLE1  WHERE ID IN (SELECT ID FROM TABLE2 WHERE MONTH(CrOnDate) = 4 GROUP BY ID) AND TYPE = 2 GROUP BY TYPE


Tx,
Hiranmaya Dash
0
hiranmaya
Asked:
hiranmaya
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess that some records in the details table have the same ID value, but different CrOnDate month values:

like master (1,2)
and details (1,2002/03/01)
and details (1,2002/04/01)

This gives for A: count=1
This gives for B1: count=1
This gives for B2: count=1

In short, some records of table2 are counted for both B1 and B2.

Solution: Rewrite your queries.
Suggestion:
SELECT TYPE, COUNT(*) AS ROWSCOUNTS
FROM TABLE1 JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
AND TABLE1.TYPE = 2 GROUP BY TYPE

-- B1.
SELECT TYPE, COUNT(*) AS ROWSCOUNTS
FROM TABLE1 JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
WHERE MONTH(TABLE2.CrOnDate) = 3
AND TYPE = 2 GROUP BY TYPE

-- B2.
SELECT TYPE, COUNT(*) AS ROWSCOUNTS
FROM TABLE1 JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
WHERE MONTH(TABLE2.CrOnDate) = 4
AND TYPE = 2 GROUP BY TYPE

CHeers
0
 
CleanupPingCommented:
hiranmaya:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now