MySQL Forums
Forum List  »  Newbie

LOTS of trouble with COUNT and UNION
Posted by: ryan blackman
Date: August 07, 2012 03:46PM

This is my query which isn't working:
(SELECT xferleads.customer, customers.customer AS customername, customers.id, qtyleads, woactive, email, xfer_no2, augment,
COUNT(xferleads.customer) as countalltime
FROM xferleads
LEFT JOIN customers ON customers.id=xferleads.customer
LEFT JOIN fulfillment ON customers.id=fulfillment.id
WHERE CHAR_LENGTH(xferleads.customer) > 0)
UNION ALL
(SELECT xferleads.customer, customers.customer AS customername,customers.id, qtyleads, woactive, email, xfer_no2, augment,
COUNT(xferleads.customer) as countdaily
FROM xferleads
LEFT JOIN customers ON customers.id=xferleads.customer
LEFT JOIN fulfillment ON customers.id=fulfillment.id
WHERE xferleads.timestamp >= CURRENT_DATE AND xferleads.timestamp < CURRENT_DATE + INTERVAL 1 DAY AND CHAR_LENGTH(xferleads.customer) > 0 GROUP BY customers.id )


I want to return the following columns like so:

customer.id | queried column |etc | etc| countdaily| countalltime |


counts are allowed to be 0 or both can be 0
the counts and groups are the problem here, i cant seem to get both count columns out nor can i seem to group each count column separately.

Options: ReplyQuote


Subject
Written By
Posted
LOTS of trouble with COUNT and UNION
August 07, 2012 03:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.