COUNT problem

[c_pattle]

I have a table called "orders" and I want to perform multiple counts on the table.  The first count I want to perform is to get the total number of records.  The second count will be to get the total records where type="everyday" and status="pending".  The third count will count the total or records where type="everyday" and status="sent". 

 

I have the following sql which works but at the moment the figures it returns seem to be wrong.  All of the counts return 1800 when there are only 34 records in the table.  Can anyone see anything wrong with my sql?

 

SELECT COUNT(o.status), COUNT(o2.status), COUNT(o3.status) FROM orders AS o LEFT JOIN orders AS o2 ON (o2.type="Everyday" AND o2.status="Pending") LEFT JOIN orders AS o3 ON (o3.type="Everyday" AND o3.status="Sent") WHERE o.company_name="RHB Exports"

 

Thanks for any help

[kickstart]

Hi

 

Think with your left joins you will square the number of rows.

 

What you would need is a UNION to do it how you want:-

 

SELECT 'All', COUNT(*)
FROM orders
WHERE company_name="RHB Exports"
UNION
SELECT 'Pending', COUNT(*)
FROM orders
WHERE type="Everyday" AND status="Pending"
AND company_name="RHB Exports"
SELECT 'Sent', COUNT(*)
FROM orders
WHERE type="Everyday" AND status="Sent" 
AND company_name="RHB Exports"

 

That would bring back 3 rows, one for each count.

 

Possibly a bit more efficient

 

SELECT 'All', COUNT(*)
FROM orders
WHERE company_name="RHB Exports"
UNION
SELECT status, COUNT(*)
FROM orders
WHERE type="Everyday" 
AND status IN ("Pending", "Sent")
AND company_name="RHB Exports"
GROUP BY status

 

All the best

 

Keith

[c_pattle]

Thanks Keith

 

Yeah that explains why my results where way out.  I tried both your solutions and they do exactly what I want so thanks!