#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171

    DISTINCT vs GROUP BY


    1 - What is the difference between the 2 queries below?
    2 - Why do the results have different order?
    Code:
    SELECT DISTINCT type
    FROM   items
    Code:
    SELECT type
    FROM   items
    GROUP  BY type
    3 - True or False: GROUP BY can be used without aggregate functions for example this is valid:
    Code:
    SELECT id
    FROM   members
    GROUP  BY name
    4 - True or False: When aggregate functions are used in a query we may or may not use GROUP BY. If we select any columns - that is not in aggregate function - then we will have to use GROUP BY. For example first 2 queries below are valid and the last one is invalid:
    Code:
    SELECT Count(*)
    FROM   members
    Code:
    SELECT Count(*),
           id
    FROM   members
    GROUP  BY id
    Code:
    SELECT Count(*),
           id
    FROM   members
    5 -True or False:This query mean calculate the avergate price of all items but unique price. For example if there are 3 items for $5, only consider 1 of them:
    Code:
    SELECT Avg(DISTINCT price)
    FROM   items
    6 -Are there other major rules about group by?

    Thank you
    Last edited by zxcvbnm; April 8th, 2013 at 09:03 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    1 none
    2 why? because neither has an ORDER BY clause, which is a sine qua non
    3 true, but the results might be confusing
    4 the way you phrased it... true
    5 true
    6 one minor rule -- all columns in the GROUP BY should also be in the SELECT clause (otherwise the results are hard to interpret)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171
    Originally Posted by r937
    One minor rule -- all columns in the GROUP BY should also be in the SELECT clause (otherwise the results are hard to interpret)
    Hello,
    Is this the re-phrase of number 4?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    Is this the re-phrase of number 4?
    not at all

    you can quite validly run this --
    Code:
    SELECT country
         , COUNT(*) AS incidents
      FROM citydata
    GROUP
        BY country
         , city
    and then get results like this --
    Code:
    US  2400
    US 10100
    US 22500
    US 93700
    CA  1500
    CA  6700
    valid, but useless

    which city in the US had 93700 incidents?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171
    1 - Is this the best solution to get the most expensive card:
    Code:
    SELECT carts.id        AS CART,
          SUM ( price * qty ) AS total
    FROM   items
           INNER JOIN cartitems
                   ON items.id = cartitems.item_id
           INNER JOIN carts
                   ON carts.id = cartitems.cart_id
    GROUP  BY cart
    ORDER  BY total DESC 
    LIMIT 1
    2 - Is there such thing is SQL:
    Code:
    SELECT carts.id        AS CART,
          MAX(SUM ( price * qty )) AS total
    FROM   items
           INNER JOIN cartitems
                   ON items.id = cartitems.item_id
           INNER JOIN carts
                   ON carts.id = cartitems.cart_id
    GROUP  BY cart
    Last edited by zxcvbnm; April 9th, 2013 at 12:03 AM.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    2 - Is there such thing is SQL
    in the interest of helping you learn sql, i suggest you try this and report what happens
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171
    Originally Posted by r937
    in the interest of helping you learn sql, i suggest you try this and report what happens
    #1630 - FUNCTION test.SUM does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual


    Also would u please show me how you would find the most expensive cart?

    Thank you
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    #1630 - FUNCTION test.SUM does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
    not sure (and too lazy to test it for you) but i would never code a space in between the name of the function and the opening parenthesis


    Originally Posted by zxcvbnm
    Also would u please show me how you would find the most expensive cart?
    in mysql? with LIMIT 1, just like you did
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171
    Yes. Space was bad. Now it says: #1111 - Invalid use of group function.
    Looks like cant use to aggregate functions together.

IMN logo majestic logo threadwatch logo seochat tools logo