Gilbert Gilbert - 3 months ago 10
MySQL Question

MySQL SUM() with three case conditional

I have a dataset and I have to perform a SUM() in MySQL but with 3 levels of GROUP BY. I have tried on my own but I can get the result. I have searched here but I have not found any suitable solution. Here is the data...

+---------+----------+---------+------+
| dev_id | skill | customer| vote |
+---------+----------+---------+------+
| 1 | .Net | Walmart | 0 |
| 1 | .Net | Walmart | 1 |
| 1 | .Net | Walmart | 1 |
| 1 | .Net | Walmart | 0 |
| 1 | .Net | Target | 0 |
| 1 | .Net | Target | 1 |
| 1 | .Net | Target | 1 |
| 1 | .Net | Target | 0 |
| 1 | Python | Walmart | 0 |
| 1 | Python | Walmart | 0 |
| 1 | Python | Walmart | 1 |
| 1 | Python | Walmart | 0 |
| 1 | Python | Target | 0 |
| 1 | Python | Target | 0 |
| 2 | .Net | Walmart | 0 |
| 2 | .Net | Walmart | 0 |
| 2 | .Net | Walmart | 0 |
| 2 | .Net | Walmart | 0 |
| 2 | .Net | Target | 0 |
| 2 | .Net | Target | 0 |
| 2 | .Net | Target | 1 |
| 2 | .Net | Target | 0 |
| 2 | Python | Walmart | 0 |
| 2 | Python | Walmart | 0 |
| 2 | Python | Walmart | 0 |
| 2 | Python | Walmart | 0 |
| 2 | Python | Target | 0 |
| 2 | Python | Target | 1 |
+---------+----------+---------+------+


I need to group by
dev_id
, then by
skill
, then by
customer
, and the
SUM()
the vote. The desire solution is something like this...

+---------+----------+---------+-------+
| dev_id | .Net | Python | Count |
+---------+----------+---------+-------+
| 1 | .Net | Walmart | 2 |
| 1 | .Net | Target | 2 |
| 1 | Python | Walmart | 1 |
| 1 | Python | Target | 0 |
| 2 | .Net | Walmart | 0 |
| 2 | .Net | Target | 1 |
| 2 | Python | Walmart | 0 |
| 2 | Python | Target | 1 |
+---------+----------+---------+-------+


Any ideas on how to solve this problem?

Answer

Did you try the query like:

select dev_id, skill, customer, sum(vote) as cnt from <table name> group by dev_id, skill, customer;