CODEWITHSUNDEEP
phpmysql
user7980186
user7980186

Reputation:

Use grouping and case after if statement in SQL query

My database is something like this, but this is a demo:

id | pkg_name  | pkg_value
1  | package 1 | 1200
2  | package 2 | 1200
3  | package 3 | 1200
4  | package 4 | 1200
5  | master    | 1400
6  | master    | 1500

And here is what I want to query:

  1. If one item is listed 2 or more times it counts as 2 only.
  2. If it's listed only 1 time so it counts as 1 only.

How can I build this query?

The query I created to count all field and group is as below:

SELECT pkg_name, count(*) from packages GROUP by pkg_name

Upvotes: 0

Views: 47

Answers (2)

BeetleJuice
BeetleJuice

Reputation: 40896

SELECT 
   pkg_name, 
   IF(COUNT(*)<=2, COUNT(*), 2) AS total
from packages GROUP by pkg_name

This should work, but there is likely a more optimized version that doesn't execute the count twice.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

You can try using a CASE expression which maps the package count to 1 if the underlying count be 1, otherwise to 2 for a count of 2 or more.

SELECT
    pkg_name,
    CASE WHEN COUNT(*) = 1 THEN 1 ELSE 2 END AS pkg_cnt
FROM packages
GROUP BY
    pkg_name

Upvotes: 0

Related Questions