LOTS of trouble with COUNT and UNION
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.