CODEWITHSUNDEEP
mysqlsqldatabasecountgroup-by
Mike2414
Mike2414

Reputation: 69

SQL count records equal to 1 value and count and group the rest

I am having trouble with a query I need to write. For some reason I cant seem to get anything to work. I know the solution is very simple but I just can't seem to think of it.

Lets say I have a table in my DB called "entrys" that looks like this:

ID  |  Item  |  value
------------------------------
1  |  item 1  |  3
2  |  item 1  |  4
3  |  item 1  |  3
4  |  item 2  |  3
5  |  item 3  |  3
6  |  item 1  |  3
7  |  item 1  |  3
8  |  item 1  |  3
9  |  item 4  |  3
10 |  item 4  |  6

I want to count the times item 1 occur where value = 3 and then group the other items together can provide a count for them that = 3 so for example the output for this table will be

  Item  |  Count
---------------------
Item 1  |  5
Other   |  3

As item 1 occurs 5 times the with the value of 3 and all other items that have the value of 3 are grouped together and displayed as other with a count of 3

Upvotes: 2

Views: 3145

Answers (3)

FuzzyTree
FuzzyTree

Reputation: 32392

Try using a derived table where your items are named either Item1 or Other. Then group the derived table by name and select the count.

SELECT
    Item,
    COUNT(*)
FROM (
  SELECT
    (CASE
        WHEN Item = 'item 1'
        THEN 'Item 1'
        ELSE 'Other'
    END) Item
  FROM items
  WHERE value = 3) t1
GROUP BY Item

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The key to the aggregation is a case statement. Then you need to count the appropriate values as well. In MySQL, you can do:

SELECT (CASE WHEN Item = 'item 1' THEN Item ELSE 'Other' END) AS Item, COUNT(*) AS `Count3`
FROM entrys e
WHERE value = 3
GROUP BY Item;

MySQL allows the use of column aliases in the GROUP BY. If you want counts of everything and where value is 3, then you can do:

SELECT (CASE WHEN Item = 'item 1' THEN Item ELSE 'Other' END) AS Item,
       COUNT(*) as AllCount, SUM(value = 3) as Count3
FROM entrys e
GROUP BY Item;

Upvotes: 2

Jaaz Cole
Jaaz Cole

Reputation: 3180

SELECT CASE Item WHEN 'item 1' THEN Item ELSE 'Other' END AS Item, COUNT(ID) AS `Count`
FROM entrys
GROUP BY CASE Item WHEN 'item 1' THEN Item ELSE 'Other' END

Upvotes: 0

Related Questions