MySQL Count 2 tables in 1 query with a where clause

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

zskull88Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nasirbestCommented:
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
zskull88Author Commented:
same thing still has
 r       i
1150       1150
0
nasirbestCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

VishnukumarCommented:
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
tikusbalapCommented:
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
tikusbalapCommented:
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
tikusbalapCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
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
zskull88Author Commented:
Sorry it took so long but this work great thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.