Irma Elita Irma Elita - 3 months ago 7
MySQL Question

How to count the same values as one sql

I have a table like this.

----------------------------
| ID | CITY | BLOCK| NAME |
----------------------------
| 1 |Jakarta| A | John |
| 2 |Jakarta| A | Rey |
| 3 |Bekasi | A | Boy |
----------------------------




What's the correct query to count total of blocks where the city is Jakarta and if there are the same blocks in Jakarta, the blocks should be counted as one?

-----------------------
| TOTAL_BLOCK_JAKARTA |
-----------------------
| 1 |
-----------------------


If I'm using this query,

SELECT COUNT(block) FROM member AS total_block_jakarta WHERE city = 'Jakarta' GROUP BY block


it will return more than one row like this.

-----------------------
| TOTAL_BLOCK_JAKARTA |
-----------------------
| 1 |
| 1 |
-----------------------


If I'm using
COUNT(DISTINCT)
in above query it will return values like this.

-----------------------
| TOTAL_BLOCK_JAKARTA |
-----------------------
| 2 |
-----------------------


Please help me to find the correct query. Thanks.

Answer

try this

SELECT COUNT(DISTINCT block) AS total_block_jakarta FROM MEMBER WHERE CITY = 'Jakarta';
Comments