0

SQL 1

SELECT (
COUNT(contract.contract_notesid) + (SELECT COUNT(contract_notesid) 
FROM quotation WHERE quotation.contract_notesid = contract.contract_notesid)

) AS total 

FROM contract where contract_notesid = '48'

total 2

SQL 2

SELECT (
COUNT(contract.contract_notesid) + (SELECT COUNT(contract_notesid) 
FROM quotation WHERE quotation.contract_notesid = 48)

) AS total 

FROM contract where contract_notesid = '48'

total 6

contract table: 2 records quotation table: 4 records

1.why diffent count?

2.SQL 2,Is there any better way to do?(total: 6)

Improve this question
3
  • 1
    Depends what you're trying to do exactly. What is it that you're trying to accomplish? Commented Nov 8, 2010 at 8:02
  • @r-dub get total from 2 tables Commented Nov 8, 2010 at 8:04
  • try manually counting contracts with contract_notesid = 48 from your table... compare your manual result to your two sql queries above... which one tallies is the correct query. Commented Nov 8, 2010 at 8:06

3 Answers 3

Reset to default
3

These queries are not equivalent. You are performing a JOIN (Cartesian product then trimming result set) but in the second one you have duplicate results since you are matching on a different condition, causing your total to be higher. The 'correct' query of these two depends on what you're trying to do..

Possible solution -

you 'could' be trying to do something like the following:

select count(contract.contract_notesid) + count(quotation.contract_notesid)
from contract
join quotation
on contract.contract_notesid = quotation.contract_notesid
where contract.contract_notesid = '48'

but this is a wild guess until you provide more info.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

@Alex The sql 2(total 6),has other way to query?
@LNXA It's 3am, I'll check back on this thread tomorrow morning maybe you'll have some more specific requirements :[
Wow, Thanks for your support. HK is 4:00 PM
0

I am not a MySQL expert (I know some Oracle), so I would abstain from guessing and answering your first question.

For your second question: What exactly are you trying to accomplish?

If you want the number of records in the quotation table + number of records in the contract table with contract_notesid = 48, then all you need is

select   
(
  (select count(contract.contract_notesid) FROM contract where contract_notesid = '48')
  +  
  (SELECT COUNT(contract_notesid) FROM quotation WHERE quotation.contract_notesid = 48)  
) as TOTAL  
from dual

If you want the count of all quotations that have a contract associated with it, for contract_notesid = 48, then you need a join:

SELECT COUNT(*) as total
  FROM quotation quotation, 
       contract contract
WHERE quotation.contract_notesid = contract.contract_notesid
  AND quotation.contract_notesid = '48'
Improve this answer

6 Comments

@Nivas sql2 total:8 only 6 in 2 tables
How many records you have in each table, for contract_notesid = '48'?
contract table: 2 records quotation table: 4 records
If the 2nd sql is returning 8, then it looks like it is doing a cartesian product. (4*2 = 8). Are you sure you copy-pasted the complete sql? The answer for the 2nd SQL should be 2 (only 2 matching records).
SQL 1: dual is the third table ? No need to use third tables.
|
0

I notice that the selection on the quotation.contract_notesid = 48, but the selection on contract is where contract_notesid = '48' - the latter is quoted, while the former is not. This implies that the quotation and contract fields are of different types, and that the query is doing some implicit conversion.

I suggest explicitly converting the numeric field to a string, in the WHERE clause of the first query.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.