Discuss Getting count and sum along w/ all original data in the MySQL Help forum on Dev Shed. Getting count and sum along w/ all original data MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
Required results:
tracker.lead_id, user.id, tracker.success, total_yes (counts if a lead_id is distinct and at least one was a success - total for all leads for that specific user), total_fill (counts if a success for a specific lead_id and user)
100, 6, 1, 2, 2
100, 6, 1, 2, 2
101, 6, 0, 2, 3
101, 6, 1, 2, 3
101, 6, 1, 2, 3
101, 6, 1, 2, 3
102. 7, 1, 1, 2
102, 7, 1, 1, 2
103, 7, 0, 1, 0
For display purposes I need to return the aggregate and full data (there is some I am leaving out buy trivial here).
Simple query I am using to get data but not aggrators.
Code:
SELECT
tracker.bf_lead_id
, tracker.success
, tracker.post_datetime
, users.first_name AS user_first_name
, users.last_name AS user_last_name
FROM tracker
INNER JOIN
users ON
users.id = tracker.user_id
WHERE
tracker.post_datetime >= "2013-01-24 12:00:00"
AND tracker.post_datetime <= "2013-01-24 13:00:00"
ORDER BY
users.id ASC
When I try to add aggregate functions, my calculations are for the entire group, not by user
Posts: 26,371
Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 6 sec
Reputation Power: 4140
i'm sorry, i did not understand your explanation of which aggregates you want
here's the general strategy -- use a UNION
there's an additional nuance for whether you want the users' aggregates to come before or after the users' detail rows, but we can go into that after you post your working UNION query
Code:
SELECT tracker.bf_lead_id
, tracker.success
, tracker.post_datetime
, NULL AS sum_1
, NULL AS count-2
, NULL AS avg_3
, users.first_name AS user_first_name
, users.last_name AS user_last_name
FROM ...
WHERE ...
UNION ALL
SELECT NULL
, NULL
, NULL
, SUM(something_1) AS sum_1
, COUNT(something_2) AS count-2
, AVG(something_3) AS avg_3
, users.first_name AS user_first_name
, users.last_name AS user_last_name
FROM ...
WHERE ...
GROUP
BY users.first_name
, users.last_name
ORDER
BY users.last_name
, users.first_name
, ???
Posts: 251
Time spent in forums: 2 Days 17 h 36 m 48 sec
Reputation Power: 64
As always thank you for your help.
Quote:
Originally Posted by r937
i'm sorry, i did not understand your explanation of which aggregates you want
It might help for me to explain the data a bit better. Each lead_id represents a data submission (going call it a "yes"). The success field represents each submission succeeded or not (going to call it a "fill"). Therefore a yes could have 0 or many fills. I want to count/sum this data two different ways:
1. I want to count the distinct number of yeses: if a lead_id has 0 or many, it still only represents on yes, by user_id.
2. I want a sum of the successful fills by user_id.
I made some changes to my query based on what you showed and I believe it is correct. (see below)
Quote:
Originally Posted by r937
there's an additional nuance for whether you want the users' aggregates to come before or after the users' detail rows, but we can go into that after you post your working UNION query
Can you explain how I can get the aggregates in the two different positions? It would be good knowledge to store.
Once again, thank you for your help and time,
oach
Code:
SELECT
tracker.bf_lead_id
, tracker.success
, tracker.post_datetime
, NULL AS count_1
, NULL AS sum_1
, users.first_name AS user_first_name
, users.last_name AS user_last_name
, users.id AS user_id
FROM tracker
INNER JOIN
users ON
users.id = tracker.user_id
WHERE
tracker.post_datetime >= "2013-01-24 12:00:00"
AND tracker.post_datetime <= "2013-01-24 13:00:00"
UNION ALL
SELECT
NULL AS bf_lead_id
, NULL AS success
, NULL AS post_datetime
, COUNT(DISTINCT tracker.bf_lead_id) AS count_1
, SUM(tracker.success) AS sum_1
, users.first_name AS user_first_name
, users.last_name AS user_last_name
, users.id AS user_id
FROM tracker
INNER JOIN
users ON
users.id = tracker.user_id
WHERE
tracker.post_datetime >= "2013-01-24 12:00:00"
AND tracker.post_datetime <= "2013-01-24 13:00:00"
GROUP BY
users.id
ORDER BY
user_last_name ASC
, user_first_name ASC
, post_datetime ASC
Posts: 26,371
Time spent in forums: 3 Months 1 Week 2 Days 6 h 29 m 6 sec
Reputation Power: 4140
Quote:
Originally Posted by oach
How do I move where the aggregates show up in the data?
adjust the ORDER BY clause
presumably you want user columns as the first sort keys, then choose one of the optional columns -- post_datetime will do nicely -- and see what happens when you change ASC to DESC
Posts: 251
Time spent in forums: 2 Days 17 h 36 m 48 sec
Reputation Power: 64
Quote:
Originally Posted by r937
adjust the ORDER BY clause
presumably you want user columns as the first sort keys, then choose one of the optional columns -- post_datetime will do nicely -- and see what happens when you change ASC to DESC
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
