[SOLVED] Use grouping and case after if statement in SQL query

Issue

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

Solution

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.

Answered By – BeetleJuice

Answer Checked By – Candace Johnson (BugsFixing Volunteer)