CODEWITHSUNDEEP
mysqlsql
Paolo_Mulder
Paolo_Mulder

Reputation: 1289

Getting The SUM() of COUNT()

Is there a way in mysql to achieve 

SELECT COUNT( SUM(field2) ) FROM TABLE WHERE field1='value' GROUP BY field2

Returning one row with the total sum of the counted field 2.

Field 1 && Field 2 are both not unique.

If there is a need for more info , just let me know.

Edit : I was not thinking clear. I need the COUNT of The COUNT.

so all the counted values from field 2. this can be achieved by getting the number of rows , but I chose for this :

SELECT COUNT( first_count ) total_count FROM 
( SELECT COUNT( field2 ) as first_count FROM TABLE
  WHERE field1='value' GROUP BY field2 )t1

Or is there a faster query?

Cheers !

Upvotes: 3

Views: 341

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

I think you mean:

SELECT field2
     , field2 * COUNT(*)
FROM TABLE
WHERE field1 = 'value'
GROUP BY field2

Also:

There is no GROUPED BY. There is: GROUP BY

After your update and clarification, I think what your query shows is the same with:

SELECT COUNT( DISTINCT field2 ) AS total_count
FROM TABLE
WHERE field1 = 'value'

Upvotes: 5

Alex K.
Alex K.

Reputation: 175768

Your update

SELECT COUNT( first_count ) total_count FROM 
( 
  SELECT COUNT( field2 ) as first_count FROM TABLE
  WHERE field1='value' GROUP BY field2 )t1
)

Is a count of the COUNTs for each field2, which is the same as a count of unique field2s;

SELECT COUNT(DISTINCT field2) FROM TABLE WHERE field1='value'

Upvotes: 5

Related Questions