Need a twin group by value

I have a query something like this,

SELECT SOME_ID, COUNT(ANOTHER_ID) AS WITHOUT_FILTER 
from SOME_TABLE GROUP BY SOME_ID

this returns me

+------------+------------------+
| SOME_ID    | WITHOUT_FILTER   |
+------------+------------------+
|  1         | 40               |
|  2         | 30               |
+------------+------------------+

I have the same query with a condition which gives me filtered values.

SELECT SOME_ID, COUNT(ANOTHER_ID) AS WITH_FILTER 
from SOME_TABLE WHERE SOME_COL > 10 GROUP BY SOME_ID

which returns obviously lesser values in the grouped_by section

+------------+----------------+
| SOME_ID    | WITH_FILTER    |
+------------+----------------+
|  1         | 20             |
|  2         | 15             |
+------------+----------------+

Now, I need a query to give me both the count values ie with condition and without condition in one single query. The result should be like this

+----------+----------------+---------------+
| SOME_ID  | WITHOUT_FILTER | WITH_FILTER   |
+----------+----------------+---------------+
|  1       | 40             | 20            |
|  2       | 30             | 15            |
+------------+--------------+---------------+

Please help me.

You can do this:

SELECT 
    SOME_ID, 
    COUNT(ANOTHER_ID) AS WITHOUT_FILTER
    SUM(case when SOME_CONDITION then 1 else 0 end) AS WITH_FILTER

from SOME_TABLE GROUP BY SOME_ID

For learning purposes, here are my 2 cents. You can use COUNT on both fields:

SELECT SOME_ID, 
       COUNT(ANOTHER_ID) AS WITHOUT_FILTER
       COUNT(case 
               when WHEN SOME_COL > 10 then ANOTHER_ID 
               else NULL 
             end) AS WITH_FILTER
from SOME_TABLE GROUP BY SOME_ID

The trick is that COUNT counts non-null values. This feature is ANSI SQL supported, BTW.

You can get a "COUNT, but only for the rows meeting a condition" effect by using IF() and SUM.

 SELECT SOME_ID, 
        COUNT(ANOTHER_ID) AS WITHOUT_FILTER,
        SUM(IF(SOME_COL > 10, 1, 0)) AS WITH_FILTER
 FROM SOME_TABLE GROUP BY SOME_ID

(Note: This solution, and all the others except Adrian's, has a subtle problem if ANOTHER_ID is ever NULL. If that's the case, then Adrian's is the only one that is truly correct).

try CASE and SUM combined:

SELECT SOME_ID, COUNT(ANOTHER_ID) as WITHOUT_FILTER,
SUM(CASE WHEN SOME_COL > 10 THEN 1 else 0 END) as WITH_FILTER
from SOME_TABLE GROUP BY SOME_ID;

SELECT T1.SOME_ID, 
       COUNT(T1.ANOTHER_ID) AS WITH_FILTER,
       COUNT(T2.ANOTHER_ID) AS WITHOUT_FILTER 
FROM   SOME_TABLE as T1, 
       SOME_TABLE as T2
WHERE 
      T1.SOME_ID=T2.SOME_ID
  AND T2.SOME_COL > 10
GROUP BY T1.SOME_ID, T2.SOME_ID

Or you can do it with 2 views merging if your conditions become too touchy