Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL server count error!!!

Posted on 2002-05-06
3
Medium Priority
?
380 Views
Last Modified: 2006-11-17
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
Comment
Question by:hiranmaya
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 6990948
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
 

Expert Comment

by:CleanupPing
ID: 9280538
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question