#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Location
    knee deep in sh*t
    Posts
    254
    Rep Power
    64

    Getting count and sum along w/ all original data


    Hey All,

    I have two tables that I want to get certain values from but I also need aggregate count() and sum() data. I am not getting to work.

    tracker table:
    id int primary
    lead_id int
    user_id int
    success tinyint (will be a 0 or 1)

    users table:
    id int primary
    firstname varchar
    lastname varchar

    Data in lead table:
    1 100 6 1
    2 100 6 1
    3 101 6 0
    4 101 6 1
    5 101 6 1
    6 101 6 1
    7 102 7 1
    8 102 7 1
    9 103 7 0

    Data in users table:
    6 Jane Doe
    7 John Doe

    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

    Thank you for your time and consideration,
    oach
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    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
         , ???

    Comments on this post

    • oach agrees : Thank you for the help!
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Location
    knee deep in sh*t
    Posts
    254
    Rep Power
    64
    As always thank you for your help.

    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)

    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
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by oach
    I made some changes to my query based on what you showed and I believe it is correct. (see below)
    yes, it looks fine

    what happened when you tested it?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Location
    knee deep in sh*t
    Posts
    254
    Rep Power
    64
    Originally Posted by r937
    yes, it looks fine

    what happened when you tested it?
    Numbers looked right. So I think it is right as well.

    How do I move where the aggregates show up in the data?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Location
    knee deep in sh*t
    Posts
    254
    Rep Power
    64
    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
    That worked a treat. Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo