?
Solved

MySQL Count 2 tables in 1 query with a where clause

Posted on 2010-04-01
9
Medium Priority
?
282 Views
Last Modified: 2012-05-09
There's only 1 in the i and 0 in the r but its returning all the results i have try if statements joins and unions. all with the same result. and nothing equals the same
select count(o.orderNum) as `r`, count(s.orderID) as `i` from `orders` o, `shoppingCart` s where s.orderID = '1166233652' or o.orderNum = '1166233652'

Open in new window

0
Comment
Question by:zskull88
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 6

Expert Comment

by:nasirbest
ID: 29378365
you need a little trick! with a dummy column you can satisfy GROUP BY

try this one
SELECT 1 AS `dummy`,
       COUNT(o.orderNum) AS `r`, 
       COUNT(s.orderID) AS `i` 
FROM `orders` o, `shoppingCart` s 
WHERE s.orderID = '1166233652' OR o.orderNum = '1166233652'
GROUP BY 1

Open in new window

0
 

Author Comment

by:zskull88
ID: 29378938
same thing still has
 r       i
1150       1150
0
 
LVL 6

Expert Comment

by:nasirbest
ID: 29379770
there is no criteria defined to prevent cartesian product.

looking again in your query I found that you are searching same order number in both tables. so you can use following


SELECT 1 AS `dummy`,
       COUNT(o.orderNum) AS `r`, 
       COUNT(s.orderID) AS `i` 
FROM `orders` o JION `shoppingCart` s 
     ON o.orderNum = s.orderID
WHERE o.orderNum = '1166233652'
GROUP BY 1

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Expert Comment

by:Vishnukumar
ID: 29382973
please try this.

select count(*) as 'r', @cnt as 'i' from orders where (@cnt) in (select @cnt:=count(*) from shoppingCart where orderID  =1166233652) and orderNum  = 1166233652;
0
 
LVL 6

Expert Comment

by:tikusbalap
ID: 29390420
select count(o.orderNum) as `r`, count(s.orderID) as `i` from `orders` o, `shoppingCart` s group by o.orderNum, s.orderID having s.orderID = '1166233652' or o.orderNum = '1166233652'
0
 
LVL 6

Expert Comment

by:tikusbalap
ID: 29390833
sorry it should be 'and' only:

select count(o.orderNum) as `r`, count(s.orderID) as `i` from `orders` o, `shoppingCart` s where s.orderID = '1166233652' and o.orderNum = '1166233652'
0
 
LVL 6

Accepted Solution

by:
tikusbalap earned 2000 total points
ID: 29393414
My mistake again, here with subquery:


select count(o.orderNum) as `r`, (select count(s.orderID) from `shoppingCart` s where s.orderID = '1166233652') as `i`
from `orders` o
where o.orderNum = '1166233652';
0
 
LVL 41

Expert Comment

by:Sharath
ID: 29398538
I think you are looking for this
SELECT (SELECT COUNT(o.orderNum) 
        FROM   `orders` o 
        WHERE  o.orderNum = '1166233652') AS `r`, 
       (SELECT COUNT(s.orderID) 
        FROM   `shoppingCart` s 
        WHERE  s.orderID = '1166233652') AS `i`

Open in new window

0
 

Author Closing Comment

by:zskull88
ID: 31710081
Sorry it took so long but this work great thanks
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

601 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