CODEWITHSUNDEEP
mysqlsqlselectcount
Charles X
Charles X

Reputation: 1

Count (Select * Join) mysql query

I'm stuck on this for hours, I'm trying to COUNT how many subscribers are there in Group A, Group B, Group C for this particular query:

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

The sub-query gets the Group area gen_data_03 from tbl_gen_info

Tables contain this information:

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    |
----------------------------------------------------------------------

Query Result

Notice that each id_subscriber has two (2) rows (one for electric, one for water). After grouping by id_subscriber: 

GROUP BY rh.id_subscriber

I got this:

Result

I tried adding COUNT before the sub-query making it:

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

but that doesn't work.

Upvotes: 0

Views: 113

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Use a subquery:

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;

Upvotes: 1

Related Questions