CODEWITHSUNDEEP
mysqlcountpercentage
walk.this_way
walk.this_way

Reputation: 167

Mysql : select count when id have multiple same value and calculate percentage

+------+---------+
| id   | object  |
+------+---------+
| 1    |    1    |
| 1    |    2    |
| 1    |    3    |
| 1    |    4    |
+------+---------+

i want to select count id where have a same value, so the result be, id 1 have 4 same value, use SELECT COUNT( * ) as id1 FROM tablename GROUP BY id

+------+
| id1  | 
+------+
| 4    |   
+------+

and i have another table :

+------+---------+
| id2  | object  |
+------+---------+
| 1    |    1    |
| 1    |    2    |
| 1    |    3    |
| 1    |    4    |
| 1    |    5    |
| 1    |    6    |
| 2    |    1    |
| 2    |    2    |
| 2    |    3    |
| 2    |    4    |
| 2    |    5    |
+------+---------+

how to select count that result will be, and i still don't know how to calculate percentage,

+------+---------+
| id1  | id2     |
+------+---------+
|  4   |    6    | # 66%
|  4   |    5    | # 80%
+------+---------+

thanks for help, master.

Upvotes: 0

Views: 375

Answers (2)

Tin Tran
Tin Tran

Reputation: 6202

Please try this sqlFiddle

SELECT T1.id as T1ID,
       T2.id as T2ID,
       T1.id1,
       T2.id2,
       T1.id1/T2.id2 * 100 as percentage
FROM
 (SELECT id,count(*) as id1
  FROM tableOne
  GROUP BY id
 )T1,
 (SELECT id,count(*) as id2
  FROM tableTwo
  GROUP BY id
 )T2

Upvotes: 1

juergen d
juergen d

Reputation: 204844

SELECT COUNT(table1.object) as id1,
       COUNT(table2.object) as id2,
       COUNT(table1.object) * 100 / COUNT(table2.object) as percentage
FROM table2
left join table1 on table2.id = table1.id
GROUP BY table2.id

Upvotes: 0

Related Questions