Using counts and sum in aggregate query

Question

UPDATE: seems like force alarm and I misinterpreted my result set and actually query returns what it is supposed to return. Oops. Thanks a lot to everyone for helping!

What's wrong with my query? The incorrect results are being produced.

SELECT
    utm_source as source,
    utm_medium as medium,
    utm_campaign as campaign,
    utm_content as content,
    COUNT(order_id) as orders,
    COUNT(DISTINCT _customer_user) as customers,
    SUM(_order_total) as revenue,
    SUM(_refund_amount) as refunded,
    SUM(_order_total) - SUM(_refund_amount) as net,
    (SUM(_order_total) - SUM(_refund_amount))/COUNT(DISTINCT _customer_user) as 'average ticket'
FROM wp_realtime_utm_tracking_utms
GROUP BY source, medium, campaign, content

It would help to show a relevant sample of expected results, actual results, and source data that led to those results... but my initial guess would be that COUNT(DISTINCT _customer_user) is the wrong divisor. — Uueerdo
– Uueerdo, Commented Aug 21, 2018 at 16:41

Arihant · Accepted Answer · 2018-08-21 17:21:43Z

1

Can you please try this query?

SELECT utm_source as source, utm_medium as medium, utm_campaign as campaign, utm_content as content, COUNT(order_id) as orders, COUNT(DISTINCT _customer_user) as customers, SUM(_order_total) as revenue, SUM(_refund_amount) as refunded, (SUM(_order_total) - SUM(_refund_amount)) as net, ((SUM(_order_total) - SUM(_refund_amount))/COUNT(DISTINCT _customer_user)) as 'average ticket' FROM wp_realtime_utm_tracking_utms GROUP BY 1,2,3,4

answered Aug 21, 2018 at 17:21

Arihant

7455 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using counts and sum in aggregate query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related