CODEWITHSUNDEEP
sql
Irma Elita
Irma Elita

Reputation: 249

Sum the result of count()

I have a table like this.

-----------------
|City    |Block |
-----------------
|Bekasi  |A1    |
|Bekasi  |A1    |  
|Jakarta |A1    |
|Jakarta |A2    |
|Bandung |A3    |
-----------------

What is the correct query if I want to count subtotal of Bekasi's block as 1, Jakarta's block as 2 and Bandung's block as 1, so it will return total values like this?

-----------------
|City    |Block |
-----------------
|Bekasi  |A1    |
|Bekasi  |A1    |  
|Jakarta |A1    |
|Jakarta |A2    |
|Bandung |A3    |
-----------------
|TOTAL   |4     |
-----------------

I tried using this query

SELECT COUNT(DISTINCT block) AS total FROM report GROUP BY city

But it will only return a result like this.

--------
|Total |
--------
|1     |
|1     |  
|2     |
--------

Please help me, thanks.

Upvotes: 0

Views: 96

Answers (3)

Horaciux
Horaciux

Reputation: 6477

select count (t) from (
select city,count(block) as t from report group by city, block) s
group by city

Upvotes: 0

Rajesh Ranjan
Rajesh Ranjan

Reputation: 537

Try This

select City,count(distinct Block) from report group by City With RollUp

Upvotes: 0

Marcus Höglund
Marcus Höglund

Reputation: 16811

This will give you the sum of the total count

select SUM(a.total)
from 
(SELECT COUNT(DISTINCT block) AS total FROM report GROUP BY city) a

And if you want the data and total in the same query you could do something like this:

select 1 as rank, city, count(city) from report group by city
union all
select 2 as rank, 'Total', SUM(a.total)
from 
(SELECT COUNT(DISTINCT block) AS total FROM report GROUP BY city) a
order by rank asc

Upvotes: 1

Related Questions