#1
  1. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,246
    Rep Power
    220

    Inner Join Group By Query


    Hi calling out for uncle Rudy.

    I want to list every day that time was spent on a task.

    For example, if on Wednesday there were 3 hours spent on task A, I want to see one row for that.

    Also if there were 2 hours spent on task B, and also a 3 hours on task B (as a new entry), I want to see 1 total row of 5 hours for task B

    Here are the tables:

    Code:
    CREATE TABLE `tasks` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `title` varchar(255) DEFAULT NULL,
      `date_added` date DEFAULT NULL,
      `user_id` int(11) DEFAULT NULL,
      `priority` int(11) DEFAULT NULL COMMENT '1 = Important, 2 = Not Important',
      `description` text,
      `status` int(11) DEFAULT '1' COMMENT '1 = In Progess, 2 = Finished',
      `due_date` date DEFAULT NULL,
      `task_type` varchar(255) DEFAULT NULL,
      `unique_id` varchar(255) DEFAULT NULL,
      `flagged` int(1) DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
    Code:
    CREATE TABLE `task_hours` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `task_id` int(11) DEFAULT NULL,
      `hours` decimal(8,1) DEFAULT NULL,
      `date` date DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8;
    Wanna confirm my query is correct.

    Code:
    SELECT tasks.id, SUM(hours) AS hours, date, title FROM task_hours
    INNER JOIN tasks ON task_hours.task_id = tasks.id
    GROUP BY task_id, date
    Thank you
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by English Breakfast Tea
    Wanna confirm my query is correct.
    what happened when you tested it? ™
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,246
    Rep Power
    220
    Hey man;

    Originally Posted by r937
    what happened when you tested it? ™
    I see expected results, then I start to wonder.....
    I think it's best I check with Rudy to make sure this is not my blind spot.
    Experience has shown seeing results is not enough. Well, that's the case for me... my IQ is 4.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by English Breakfast Tea
    I see expected results,
    expected as in, you checked the numbers **by hand** and came up with the same answer

    or expected as in, the query ran and the results look like they must've come from those tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,246
    Rep Power
    220
    both
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by English Breakfast Tea
    both
    and your conclusion based on this is,,, ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,632
    Rep Power
    1811
    It may be just be me but I think I'd swap the order of the GROUP BY to be date, task_id as you are interested with tasks within dates, so date would be the primary driver.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,953
    Rep Power
    381
    Well the answer depends on whether or not there's a functional dependency on title and any of the grouped columns.
  16. #9
  17. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,246
    Rep Power
    220
    I find myself in this situation over and over again.

    I wish Rudy wasn't such a tease tease tease until he's got me on my knees.

    The results I get "look right" when I count by hand.

    Code:
    SELECT jvzoo_cproditem, 
         COUNT(transaction_type) AS type_counter,
         transaction_type
     FROM transactions 
         WHERE email = 'some_email@email.com' AND
    	            jvzoo_cproditem = 274037 OR
    		    jvzoo_cproditem = 274446 OR
    		    jvzoo_cproditem = 274450 OR
    		    jvzoo_cproditem = 274448
         GROUP BY jvzoo_cproditem, transaction_type
    Code:
    CREATE TABLE `transactions` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `transaction_time` varchar(255) NOT NULL,
      `receipt` varchar(255) NOT NULL,
      `transaction_type` varchar(255) NOT NULL,
      `affiliate` varchar(255) NOT NULL,
      `payment_method` varchar(255) NOT NULL,
      `item_no` int(11) NOT NULL,
      `recurring` varchar(255) NOT NULL,
      `line_item_type` varchar(255) NOT NULL,
      `first_name` varchar(255) NOT NULL,
      `stripe_price` decimal(4,2) DEFAULT NULL,
      `email` varchar(255) NOT NULL,
      `upsell_original_receipt` varchar(255) NOT NULL,
      `upsell_flow_id` int(11) unsigned zerofill NOT NULL,
      `username` varchar(255) NOT NULL,
      `notes` text NOT NULL,
      `ontraport_user_id` int(11) DEFAULT NULL,
      `payment_system` varchar(255) DEFAULT NULL,
      `stripe_order_id` varchar(255) DEFAULT NULL,
      `stripe_description` text,
      `ontraport_id_from_srtipe` int(11) DEFAULT NULL,
      `stripe_meta_data_contact_id` int(11) DEFAULT NULL,
      `status_in_our_db` varchar(255) DEFAULT 'active',
      `jvzoo_cprodtitle` varchar(255) DEFAULT NULL,
      `jvzoo_cproditem` varchar(255) DEFAULT NULL,
      `jvzoo_ctransaffiliate` varchar(255) DEFAULT NULL,
      `jvzoo_ctransamount` int(11) DEFAULT NULL,
      `jvzoo_ctranspaymentmethod` varchar(4) DEFAULT NULL,
      `jvzoo_ctransvendor` varchar(255) DEFAULT NULL,
      `jvzoo_cupsellreceipt` varchar(255) DEFAULT NULL,
      `jvzoo_caffitid` varchar(255) DEFAULT NULL,
      `date` date DEFAULT NULL,
      `transaction_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2217 DEFAULT CHARSET=utf8 COMMENT='ALTER TABLE employees\n  ADD last_name VARCHAR(50);\n\n`id` int(11) NOT NULL AUTO_INCREMENT,\n  `transaction_time` varchar(255) NOT NULL,\n  `receipt` varchar(255) NOT NULL,\n  `transaction_type` varchar(255) NOT NULL,\n  `affiliate` varchar(255) NOT NULL,\n  `payment_method` varchar(255) NOT NULL,\n  `item_no` int(11) NOT NULL,\n  `recurring` varchar(255) NOT NULL,\n  `line_item_type` varchar(255) NOT NULL,\n  `first_name` varchar(255) NOT NULL,\n  `stripe_price` decimal(4,2) DEFAULT NULL,\n  `email` varchar(255) NOT NULL,\n  `upsell_original_receipt` varchar(255) NOT NULL,\n  `upsell_flow_id` int(11) unsigned zerofill NOT NULL,\n  `username` varchar(255) NOT NULL,\n  `notes` text NOT NULL,\n  `ontraport_user_id` int(11) DEFAULT NULL,\n  `payment_system` varchar(255) DEFAULT NULL,\n  `stripe_order_id` varchar(255) DEFAULT NULL,\n  `stripe_description` text,\n  `ontraport_id_from_srtipe` int(11) DEFAULT NULL,\n  `stripe_meta_data_contact_id` int(11) DEFAULT NULL,\n  `status_in_our_db` varchar(255) DEFAULT ''active'',\n  `jvzoo_cprodtitle` varchar(255) DEFAULT NULL,\n  `jvzoo_cproditem` varchar(255) DEFAULT NULL,\n  `jvzoo_ctransaffiliate` varchar(255) DEFAULT NULL,\n  `jvzoo_ctransamount` int(11) DEFAULT NULL,\n  `jvzoo_ctranspaymentmethod` varchar(4) DEFAULT NULL,\n  `jvzoo_ctransvendor` varchar(255) DEFAULT NULL,\n  `jvzoo_cupsellreceipt` varchar(255) DEFAULT NULL,\n  `jvzoo_caffitid` varchar(255) DEFAULT NULL,';
    Last edited by English Breakfast Tea; August 21st, 2017 at 06:14 AM.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by English Breakfast Tea
    I find myself in this situation over and over again.
    so what are you doing about it, if you find it so disconcerting?

    Originally Posted by English Breakfast Tea
    The results I get "look right" when I count by hand.
    impossiboru

    you are getting ~crap~ results because of this **semantic error**
    Code:
         WHERE email = 'some_email@email.com' AND
    	            jvzoo_cproditem = 274037 OR
    		    jvzoo_cproditem = 274446 OR
    		    jvzoo_cproditem = 274450 OR
    		    jvzoo_cproditem = 274448
    not to dump all over you, man, but this is an error at the SQL 101 level
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,246
    Rep Power
    220
    Originally Posted by r937
    not to dump all over you, man, but this is an error at the SQL 101 level
    Rudy, your dump feels like summer rain. I got thick skin xo

    Originally Posted by r937
    impossiboru
    you are getting ~crap~ results because of this **semantic error**
    Code:
         WHERE email = 'some_email@email.com' AND
    	            jvzoo_cproditem = 274037 OR
    		    jvzoo_cproditem = 274446 OR
    		    jvzoo_cproditem = 274450 OR
    		    jvzoo_cproditem = 274448
    I pasted the query prematurely.
    Code:
     SELECT jvzoo_cproditem,
           Count(transaction_type) AS counter,
           transaction_type,
           jvzoo_cprodtitle
    FROM   transactions
    WHERE  email = ?
           AND ( jvzoo_cproditem = 274037
                  OR jvzoo_cproditem = 274446
                  OR jvzoo_cproditem = 274450
                  OR jvzoo_cproditem = 274448 )
           AND stats_in_our_database = 'active'
    GROUP  BY jvzoo_cproditem,
              transaction_type
    Originally Posted by r937
    so what are you doing about it, if you find it so disconcerting?
    No errors. I am just checking making sure what I am doing makes sense and gets Limeback approval seal.

    Am I dreaming? Make it hurt.
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    FYI you can shorten this --
    Code:
    AND ( jvzoo_cproditem = 274037
                  OR jvzoo_cproditem = 274446
                  OR jvzoo_cproditem = 274450
                  OR jvzoo_cproditem = 274448 )
    to this --
    Code:
    AND jvzoo_cproditem IN ( 274037, 274446, 274450, 274448 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo