Jump to content

COUNT problem


c_pattle

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/244130-count-problem/
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/244130-count-problem/#findComment-1253899
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.