[RESOLVED] multiple COUNT()s in one query

[perpetualshaun]

Is there any way to return TWO COUNT()s in the same MySQL query? I'm trying to come up w/ a query where I can display the number of items in an order, the raw $$ total, AND the number of backorder items on each order. Here's the query I have, but it's returning the wrong numbers:

SELECT cart_ordr_hdr.ordr_id, cart_ordr_hdr.ordr_id, cart_ordr_hdr.ordr_received_on, cart_ordr_hdr.ordr_processed_on, cart_ordr_hdr.ordr_shpped_on, cart_ordr_hdr.ordr_bllto_fname, cart_ordr_hdr.ordr_bllto_lname, cart_ordr_hdr.ordr_bllto_co, cart_ordr_hdr.ordr_bllto_phone, cart_ordr_hdr.ordr_bllto_email, cart_ordr_hdr.ordr_cart_dnld, cart_ordr_hdr.ordr_shpto_fname, cart_ordr_hdr.ordr_shpto_lname, cart_ordr_hdr.ordr_shpto_co, cart_ordr_hdr.ordr_shpto_phone, cart_ordr_hdr.ordr_shpto_email, 
COUNT(cart_ordr_itms.itm_id) AS ordr_itm_count , 
SUM(cart_ordr_itms.itm_prd_price*cart_ordr_itms.itm_qty_requested) AS ordr_total,
COUNT(cart_bkordr_itms.itm_prd_active_on) AS backorder_count
FROM cart_ordr_hdr
LEFT JOIN cart_ordr_itms ON cart_ordr_hdr.ordr_id=cart_ordr_itms.ordr_id
LEFT JOIN cart_ordr_itms AS cart_bkordr_itms ON cart_ordr_hdr.ordr_id=cart_bkordr_itms.ordr_id AND cart_bkordr_itms.itm_prd_active_on > 0
WHERE 1
GROUP BY cart_ordr_hdr.ordr_id
ORDER BY ordr_received_on DESC 
LIMIT 0,50

For the data I have in our database, I should be getting an ordr_itm_count and backorder_count of 1 and 1 for my first result, and then 3 and 2. Instead, this query is returning an item_count of 6 AND a backorder_count of 6 for my second result set.

What can I do to get the correct counts?

[perpetualshaun]

Okay, so a friend told me that I can't do it w/ one query but that I should be able to do it w/ a nested query. We are running MySQL 4.1.7 on this server, so I think I should be able to pull this off. I thought I had it for a second, but I can't get it to return the right counts. For example, if I run just part of it:

SELECT cart_ordr_hdr. * ,
COUNT( cart_ordr_itms.itm_id ) AS ordr_itm_count,
SUM( cart_ordr_itms.itm_prd_price * cart_ordr_itms.itm_qty_requested ) AS ordr_total
FROM cart_ordr_hdr
LEFT JOIN cart_ordr_itms ON cart_ordr_hdr.ordr_id = cart_ordr_itms.ordr_id
WHERE 1
GROUP BY cart_ordr_hdr.ordr_id
ORDER BY ordr_received_on DESC
LIMIT 0 , 50

This returns the item_counts of 3 and 1 for the two orders that are in the database. And then I can run this query:

SELECT COUNT( itm_prd_id ) AS backorder_count
FROM cart_ordr_itms AS cart_bkordr_itms1
LEFT JOIN cart_ordr_hdr ON cart_ordr_hdr.ordr_id = cart_bkordr_itms1.ordr_id
WHERE cart_ordr_hdr.ordr_id = cart_bkordr_itms1.ordr_id AND cart_bkordr_itms1.itm_prd_active_on >0
GROUP BY cart_ordr_hdr.ordr_id
ORDER BY ordr_received_on DESC  LIMIT 0 , 30

And I get the correct backorder_counts of 2 and 1 for the two orders. Those are the correct counts from each query. But when I combine them into one, nested query like this:

SELECT cart_ordr_hdr.*,
COUNT( cart_ordr_itms.itm_id ) AS ordr_itm_count,
SUM( cart_ordr_itms.itm_prd_price * cart_ordr_itms.itm_qty_requested ) AS ordr_total,
cart_bkordr_itms.backorder_count
FROM cart_ordr_hdr
LEFT JOIN cart_ordr_itms ON cart_ordr_hdr.ordr_id = cart_ordr_itms.ordr_id

INNER JOIN (
SELECT COUNT(itm_prd_id) AS backorder_count
FROM cart_ordr_itms AS cart_bkordr_itms1
LEFT JOIN cart_ordr_hdr ON cart_ordr_hdr.ordr_id = cart_bkordr_itms1.ordr_id
WHERE cart_ordr_hdr.ordr_id = cart_bkordr_itms1.ordr_id AND cart_bkordr_itms1.itm_prd_active_on >0
GROUP BY cart_ordr_hdr.ordr_id
ORDER BY ordr_received_on DESC 
) AS cart_bkordr_itms

WHERE 1
GROUP BY cart_ordr_hdr.ordr_id
ORDER BY ordr_received_on DESC
LIMIT 0 , 50

... it returns item counts of 6 and 2, and then backorder_counts of 2 and 2. Any ideas??

Thanks,
Shaun

[dougal85]

Why not simple things up by making it into 'two' queries, then use a union to combine them - effectively making it two in one?

I'm finding it quite hard to actually follow for problem without a database schema to follow.

So just a quick suggestion.

[perpetualshaun]

Okay, I think I'm getting closer here, but still missing... something. I tried adding UNION to my query, but couldn't get it to work. Here's an example of what I'm trying to do:

SELECT cart_ordr_hdr.ordr_id, COUNT( itm_prd_id ) AS backorder_count
FROM cart_ordr_itms AS cart_bkordr_itms
LEFT JOIN cart_ordr_hdr ON cart_ordr_hdr.ordr_id = cart_bkordr_itms.ordr_id
WHERE cart_ordr_hdr.ordr_id = cart_bkordr_itms.ordr_id AND cart_bkordr_itms.itm_prd_active_on >0
GROUP BY cart_ordr_hdr.ordr_id LIMIT 0 , 30

This returns a result set like this:

[code]

| ordr_id | backorder_count |

| 129 | 1 |

| 130 | 2 |

| 134 | 1 |

[/code]

So I want to JOIN / UNION that query w/ this one:

SELECT cart_ordr_hdr.ordr_id, COUNT( cart_ordr_itms.itm_id ) AS ordr_itm_count, SUM( cart_ordr_itms.itm_prd_price * cart_ordr_itms.itm_qty_requested ) AS ordr_total
FROM cart_ordr_hdr
LEFT JOIN cart_ordr_itms ON cart_ordr_hdr.ordr_id = cart_ordr_itms.ordr_id
WHERE 1
GROUP BY cart_ordr_hdr.ordr_id LIMIT 0 , 30

Which returns results like this...

[code]

| ordr_id | ordr_itm_count | ordr_total |

| 129 | 1 | 75.00 |

| 130 | 3 | 272.50 |

| 131 | 1 | 1397.25 |

| 132 | 1 | 310.50 |

| 133 | 1 | 22.50 |

| 134 | 1 | 200.00 |

| 135 | 1 | 22.50 |

[/code]

So see how order #130 has an item count of 3 and backorder_count of 2? How can I get all of those things together, in one query? We are running MySQL 4.1.7 on this server.

[dougal85]

Well a union wont work with joing querys with different numbers of collumns.

SELECT cart_ordr_hdr.ordr_id, COUNT( itm_prd_id ) AS backorder_count, '0' AS order_total

if modify the first query to do that at the start then it will add an extra collumn of just 0 values. then when you union them ... it should work, i think 🙂

[perpetualshaun]

Hmm... I'm trying the UNION thing here, and once again each 'part' will return the results I'm expecting, but I get an error when I try to combine them. Here's the query I'm trying to run now:

So here I am trying to get the UNION statment to work, and I'm trying a query like this:

SELECT cart_ordr_hdr.ordr_id AS listing_ordr_id,
COUNT( cart_ordr_itms.itm_id ) AS ordr_itm_count,
SUM( cart_ordr_itms.itm_prd_price * cart_ordr_itms.itm_qty_requested ) AS ordr_total,
'0' AS backorder_count
FROM cart_ordr_hdr
LEFT JOIN cart_ordr_itms ON cart_ordr_hdr.ordr_id = cart_ordr_itms.ordr_id
WHERE 1
GROUP BY cart_ordr_hdr.ordr_id

UNION (

SELECT cart_ordr_hdr.ordr_id, COUNT( itm_prd_id ) AS backorder_count
FROM cart_ordr_itms AS cart_bkordr_itms
LEFT JOIN cart_ordr_hdr ON cart_ordr_hdr.ordr_id = cart_bkordr_itms.ordr_id AND cart_bkordr_itms.itm_prd_active_on >0
WHERE cart_ordr_hdr.ordr_id= listing_ordr_id
GROUP BY listing_ordr_id

) 

But when I run this through PHP MyAdmin, it automatically tacks the LIMIT 0,30 onto the end and then spits out this error:

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LIMIT 0, 30' at line 11

Any ideas?

[onebearcat]

stupid question here - what happens when you try the query in a script or command line that way it eliminates the automatic LIMIT that is being added by PHP MyAdmin?

[dougal85]

Union doesnt have brackets. try :

SELECT cart_ordr_hdr.ordr_id AS listing_ordr_id,
COUNT( cart_ordr_itms.itm_id ) AS ordr_itm_count,
SUM( cart_ordr_itms.itm_prd_price * cart_ordr_itms.itm_qty_requested ) AS ordr_total,
'0' AS backorder_count
FROM cart_ordr_hdr
LEFT JOIN cart_ordr_itms ON cart_ordr_hdr.ordr_id = cart_ordr_itms.ordr_id
WHERE 1
GROUP BY cart_ordr_hdr.ordr_id

UNION 

SELECT cart_ordr_hdr.ordr_id, COUNT( itm_prd_id ) AS backorder_count
FROM cart_ordr_itms AS cart_bkordr_itms
LEFT JOIN cart_ordr_hdr ON cart_ordr_hdr.ordr_id = cart_bkordr_itms.ordr_id AND cart_bkordr_itms.itm_prd_active_on >0
WHERE cart_ordr_hdr.ordr_id= listing_ordr_id
GROUP BY listing_ordr_id

if that doesnt work its probably the datatypes in the columns dont match so they cant be combined.

[perpetualshaun]

Man... getting closer on this query, but something's still a little off. Here's what I have now:

SELECT cart_ordr_hdr.ordr_id, COUNT( cart_ordr_itms.itm_id ) AS ordr_itm_count, SUM( cart_ordr_itms.itm_prd_price * cart_ordr_itms.itm_qty_requested ) AS ordr_total, '0' AS backorder_count
FROM cart_ordr_hdr
LEFT JOIN cart_ordr_itms ON cart_ordr_hdr.ordr_id = cart_ordr_itms.ordr_id
WHERE 1
GROUP BY cart_ordr_hdr.ordr_id

UNION

SELECT cart_ordr_hdr.ordr_id, '0' AS ordr_itm_count, '0' AS ordr_total, COUNT( itm_prd_id ) AS backorder_count
FROM cart_ordr_itms AS cart_bkordr_itms
LEFT JOIN cart_ordr_hdr ON cart_ordr_hdr.ordr_id = cart_bkordr_itms.ordr_id AND cart_bkordr_itms.itm_prd_active_on >0
GROUP BY cart_ordr_hdr.ordr_id LIMIT 0 , 30

And it gives me results like this...

---

| ordr_id | ordr_itm_count | ordr_total | backorder_count |

| 131 | 1 | 1397.25 | 0 |

| 132 | 1 | 310.5 | 0 |

| 133 | 1 | 22.5 | 0 |

| 137 | 1 | 60 | 0 |

several more rows w/ 0 in the backorder_count column, and then it starts to show something like this:

---

| ordr_id | ordr_itm_count | ordr_total | backorder_count |

| NULL | 0 | 0 | 4 |

| 137 | 0 | 0 | 1 |

| 140 | 0 | 0 | 2 |

| 141 | 0 | 0 | 1 |

So how do I get those TOGETHER so that the same order number is only in the result set once w/ the correct numbef for item count, $$ total AND backorder_item_count?

[perpetualshaun]

Man... this is so frustrating. Maybe there simply isn't a way to do this, but it seems like it should be, to me. Here's the part of my query that's working:

SELECT cart_ordr_hdr.ordr_id,
COUNT( cart_ordr_itms.itm_id ) AS ordr_itm_count,
SUM( cart_ordr_itms.itm_prd_price * cart_ordr_itms.itm_qty_requested ) AS ordr_total
FROM cart_ordr_hdr
LEFT JOIN cart_ordr_itms ON cart_ordr_hdr.ordr_id = cart_ordr_itms.ordr_id
WHERE 1
GROUP BY cart_ordr_hdr.ordr_id LIMIT 0 , 30

So all I want to do from here is pull in the count of the backordered items. Why can't I do a RIGHT JOIN to get that count? I tried this:

SELECT cart_ordr_hdr.ordr_id,
COUNT( cart_ordr_itms.itm_id ) AS ordr_itm_count,
SUM( cart_ordr_itms.itm_prd_price * cart_ordr_itms.itm_qty_requested ) AS ordr_total,
COUNT( cart_bkordr_itms.itm_prd_id ) AS backorder_count
FROM cart_ordr_hdr
LEFT JOIN cart_ordr_itms ON cart_ordr_hdr.ordr_id = cart_ordr_itms.ordr_id
RIGHT JOIN cart_ordr_itms AS cart_bkordr_itms ON cart_ordr_hdr.ordr_id = cart_bkordr_itms.ordr_id AND cart_bkordr_itms.itm_prd_active_on >0
WHERE 1
GROUP BY cart_ordr_hdr.ordr_id LIMIT 0 , 30

... but that messed up my order sums, and the backorder_count is 26 or 27 all the way down the screen, and it should only be 1s and 2s. Does anyone have any ideas?