how do i find multiple count of value for rows in sql?

Question

I have two table in sql database like below:

Table 1 : event table          Table 2 : game table

|event_id | game_id |         |game_id | game_name |
|  140    |  10     |         |  10    | football  |
|  140    |  10     |         |  11    | Cricket   |
|  140    |  10     |
|  140    |  10     |
|  140    |  11     |
|  140    |  11     |
|  140    |  11     |

Now i want the result like below:

|game_name | Count |
| football |   4   |
| Cricket  |   3   |

I have tried the query:

SELECT g.g_name, count(g.g_name) FROM game_table g INNER JOIN event_table e ON g.game_id = e.game_id WHERE event_id = '140';

but this query is given me total count not particular count of particular game name. so how can i find counts of particular game_name?

Your query is fine, just add GROUP BY g.g_name

sagi
– sagi

2016-10-06 13:05:24 +00:00
Commented Oct 6, 2016 at 13:05 — sagi
– sagi, Commented Oct 6, 2016 at 13:05
@YourCommonSense, Amazing it is working.. thanks buddy.

Santosh Khatri
– Santosh Khatri

2016-10-06 13:09:16 +00:00
Commented Oct 6, 2016 at 13:09 — Santosh Khatri
– Santosh Khatri, Commented Oct 6, 2016 at 13:09

Mudassir Hasan · Accepted Answer · 2016-10-06 13:03:27Z

1

You need to have GROUP BY clause

SELECT g.g_name, count(g.g_name) 
FROM game_table g INNER JOIN event_table e ON g.game_id = e.game_id 
WHERE event_id = '140'
GROUP BY g.g_name;

answered Oct 6, 2016 at 13:03

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how do i find multiple count of value for rows in sql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related