CODEWITHSUNDEEP
mysqlaggregate-functions
user2967389
user2967389

Reputation: 53

summarize counts from multiple columns in a single table

I want to display count of data of two category on single display

my table like below :

table : funding
area        client      Donatur
-----------------------------------------
A        Ox      Mr.X
A        Pr      Mr.Y
A        Qs      Mr.Z
A        Ts      Mr.Z   
B        Rt      Mr.X
C        Ss      Mr.X   
C        Sa      Mr.Z

I want to display data to count based on area and donatur in on table like bellow 

Donatur     Count of Area   Count of Client
--------------------------------------------------------
Mr.X          3             3
Mr.Y          1             1
Mr.Z          2             3

I have try to create query like below :

select count(client),count(area) from funding group by area,donatur

But i don't get any right number on one side, the result like below , same on both of them. It' should be like above

Donatur     Count of Area       Count of Client
--------------------------------------------------------
Mr.X          3             3
Mr.Y          1             1
Mr.Z          2             2

Upvotes: 2

Views: 91

Answers (2)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

mysql> SELECT DONATUR,COUNT(DISTINCT AREA) AS AREA,COUNT(DISTINCT CLIENT) AS CLIENT FROM funding GROUP BY Donatur;
+---------+------+--------+
| DONATUR | AREA | CLIENT |
+---------+------+--------+
| Mr.X    |    3 |      3 |
| Mr.Y    |    1 |      1 |
| Mr.Z    |    2 |      3 |
+---------+------+--------+
3 rows in set (0.00 sec)

Upvotes: 3

juergen d
juergen d

Reputation: 204784

select donatur, count(distinct area), count(distinct client)
from funding 
group by donatur

Upvotes: 2

Related Questions