mysql - 计数(Select * Join)mysql查询

Question

我在这个问题上坚持了几个小时，我正在尝试COUNT针对此特定查询，A 组、B 组、C 组中有多少订阅者:

SELECT rh.id_subscriber, rh.bill_month, rh.bill_year,
 ( 
     SELECT tbl_gen_info.gen_data_03 
     FROM tbl_subscriber 
     LEFT JOIN tbl_gen_info ON tbl_subscriber.bill_area_code = tbl_gen_info.gen_data_01
     WHERE rh.id_subscriber = tbl_subscriber.id_subscriber

 ) AS group_area

FROM tbl_reading_head AS rh
WHERE rh.id_soa_head IS NULL 
AND rh.read_status <> 'Beginning' 
AND rh.rec_status = 'active'
ORDER BY rh.id_subscriber

子查询从tbl_gen_info获取Group区域gen_data_03

表格包含以下信息:

tbl_gen_info
--------------------------------------------
| gen_category | gen_data_01 | gen_data_03 |
--------------------------------------------
| Area Code    | Camacho St. | Group A     |
--------------------------------------------

tbl_subscriber
----------------------------------
| id_subscriber | bill_area_code | 
----------------------------------
|        1      | Camacho St.    | 
----------------------------------

tbl_reading_head
----------------------------------------------------------------------
| id_subscriber | id_soa_head | read_status | bill_month | bill_year |
----------------------------------------------------------------------
|        1      |     NULL    |   Metered   |     10     |   2017    |
----------------------------------------------------------------------

请注意，每个 id_subscriber 有两 (2) 行(一行用于电力，一行用于水)。按 id_subscriber 分组后:

GROUP BY rh.id_subscriber

我得到了这个:

我尝试在子查询之前添加 COUNT:

  COUNT(SELECT tbl_gen_info.gen_data_03 ...) AS group_area

但这不起作用。

Answer 1

使用子查询:

SELECT rh.group_area, COUNT(*)
FROM (SELECT rh.id_subscriber, rh.bill_month, rh.bill_year,
             (SELECT tbl_gen_info.gen_data_03 
              FROM tbl_subscriber LEFT JOIN
                   tbl_gen_info
                   ON tbl_subscriber.bill_area_code = tbl_gen_info.gen_data_01
             WHERE rh.id_subscriber = tbl_subscriber.id_subscriber
            ) as group_area
      FROM tbl_reading_head rh
      WHERE rh.id_soa_head IS NULL AND
            rh.read_status <> 'Beginning' AND
            rh.rec_status = 'active'
     ) rh
GROUP BY rh.group_area;