Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    44

    Unanswered: How to get a SUM from a COUNT(DISTINCT) query

    Hi!

    I'm new to the forums, and have a question.

    Code:
    SELECT COUNT(DISTINCT field1) as amount, fix_id
    FROM table 
    WHERE other_id = 1
    GROUP BY fix_id
    This query result in something like:

    amount fix_id
    1 1
    1 2
    1 3
    1 4
    1 5
    1 6
    1 7
    1 8
    1 9
    1 10
    1 11
    1 12
    1 13
    3 14
    3 15
    2 16
    1 17
    2 18
    1 19
    1 20

    Now, I would like to have the SUM of the amount column (26 in this case). Actually, I'm satisfied only with the SUM and nothing else, I don't need anything else.

    I hope I described it properly. Thanks for any kind of help.

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Code:
    SELECT SUM( amount) 
    FROM (
      SELECT COUNT(DISTINCT field1) as amount, fix_id
      FROM table 
      WHERE other_id = 1
      GROUP BY fix_id
    ) x

  3. #3
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by kordirko View Post
    Code:
    SELECT SUM( amount) 
    FROM (
      SELECT COUNT(DISTINCT field1) as amount, fix_id
      FROM table 
      WHERE other_id = 1
      GROUP BY fix_id
    ) x
    Thanks a lot, it worked perfect!

    Can I please ask what the x in the end of the query means?

  4. #4
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by Haydn View Post
    Thanks a lot, it worked perfect!

    Can I please ask what the x in the end of the query means?
    This is an alias of the subquery.- just a name, like a table name, you can use this name in your query in the same way as the table name.
    If you don't put the alias after the subquery, MySql complains and shows an error.

    Look at this example:
    Code:
    SELECT alias.col1, alias.col2
    FROM tablename alias;
    
    SELECT alias.col1, alias.col2
    FROM (
      ... subquery goes here
    ) alias
    Last edited by kordirko; 02-19-12 at 14:19.

  5. #5
    Join Date
    Feb 2012
    Posts
    44
    That seems like a smart way to get a query from a sub-query.

    Thanks a lot!

Posting Permissions

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