Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: SELECT COUNT of MAX (GROUP BY)

    Hello

    I want to get the COUNT of

    Code:
     
    SELECT MAX(id) AS ids, Name,  Version,  Pack, Serial 
    FROM Products 
    GROUP BY Name,  Version,  Pack, Serial
    SELECT COUNT(MAX(id) AS ids) AS countIds, Name, Version, Pack, Serial
    FROM Products
    GROUP BY Name, Version, Pack, Serial

    doesnt work

    thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the answer is 1

    since you've already applied GROUP BY to Name, Version, Pack, Serial, there's only going to be one MAX(id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    but I want the count of DISTINCT (Name, Version, Pack, Serial )

    if i try

    SELECT count(id) AS ids
    FROM Products
    GROUP BY Name, Version, Pack, Serial


    i get 300 rows = 1
    i need 1 row = 300


    thank you

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anselme
    but I want the count of DISTINCT (Name, Version, Pack, Serial )
    but you didn't say that
    Code:
    select count(*) as final_rows
      from (
           SELECT MAX(id) AS ids, Name,  Version,  Pack, Serial 
           FROM Products 
           GROUP BY Name,  Version,  Pack, Serial
           ) as d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    perfect ! it's a joy to speak with you !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •