Mysql – Multiple COUNT fields, GROUP BY

countgroup byjoin;MySQLsubquery

I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries?

I'm aware of WITH ROLLUP but can't produce the right query.

This gives the right result:

SELECT A.mid, B.pid, A.count AS mid_count, B.count as pid_count
  FROM (Select mid , COUNT(*) AS count FROM test GROUP BY mid) AS A,
  (Select mid, pid, COUNT(*) AS count FROM test GROUP BY mid, pid) AS B 
  WHERE A.mid = B.mid ORDER BY mid_count DESC

This is the expected output

+-----+-----+-----------+-----------+
| mid | pid | mid_count | pid_count |
+-----+-----+-----------+-----------+
|   3 |  12 |         5 |         2 |
|   3 |  14 |         5 |         1 |
|   3 |  15 |         5 |         2 |
|   4 |  12 |         2 |         1 |
|   4 |  17 |         2 |         1 |
+-----+-----+-----------+-----------+

I've created a fiddle at: http://sqlfiddle.com/#!9/e3ebe1/12

Best Answer

I removed one of the subqueries from yours but fundamentally as mysql doesn't support count over partition etc, it will need more than one pass through the table:

Select test.mid, test.pid, A.cnt as midCount, count(*) as pidCount
from test join
(Select mid, count(*) as cnt from test group by mid) A
on test.mid =  A.mid
Group by mid, pid

Related Solutions

Mysql – Selecting multiple columns from multiple rows in one column (subquery?)

You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT

SELECT CONCAT(id,' # ',name,' # ',citizens) listing FROM
(
    SELECT A.id,A.name,GROUP_CONCAT(B.name,' ',B.surname) citizens
    FROM city A INNER JOIN citizen B
    ON A.id = B.city_id GROUP BY A.id,A.name
) AA;

Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/9f4b3/1

If the names become too long due to truncation, you will have to extend GROUP_CONCAT's maximum length. To set it to 16K, run the following:

SET group_concat_max_len = 1024 * 16;
SET GLOBAL group_concat_max_len = 1024 * 16;

First line is for the session, the second is for all new DB Connections.

Postgresql – Postgres Nested WHEN Aggregate Function

After some processing this boiled down to:

While your predicate d."SettlementPointName" = 'John' is filtering a single value for "SettlementPointName" anyway, simplify to:

SELECT count(                                     d."SettlementPointPrice" < 10.5 OR NULL) AS da_00_10
     , count(d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" < 20.5 OR NULL) AS da_11_20
     , count(d."SettlementPointPrice" >= 20.5 AND d."SettlementPointPrice" < 30.5 OR NULL) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."SettlementPointName" = 'John'
AND    d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour";

About the counting technique:

For absolute performance, is SUM faster or COUNT?

Or better, yet, use the new aggregate filter technique in pg 9.4:

SELECT d."SettlementPointName"
     , count(*) FILTER (WHERE d."SettlementPointPrice" <  10.5) AS da_00_10
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 10.5
                        AND   d."SettlementPointPrice" <  20.5) AS da_11_20
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 20.5
                        AND   d."SettlementPointPrice" <  30.5) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour"
GROUP  BY 1;

This time, selecting all names and returning one row per name like you asked in the comment.

Details for FILTER:

Return counts for multiple ranges in a single SELECT statement

Best Answer

Related Solutions

Mysql – Selecting multiple columns from multiple rows in one column (subquery?)

Postgresql – Postgres Nested WHEN Aggregate Function

Related Question