CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate-functions
Channaveer Hakari
Channaveer Hakari

Reputation: 2927

count of id on status coloumn with sum of particular rows count using GROUP BY IN MYSQL

I am having the following mysql table 

id  status
------------
1   available
2   hold
3   available
4   so-hold
5   so-hold
6   hold
7   hold
8   available

When I use GROUP BY on STATUS coloumn I got the following :

count(id)   status
---------------------
3           available
3           hold
2           so-hold

Under status coloumn 'hold' and 'so-hold' belongs to the same category so I need count in the following manner : 

count(id)   status
---------------------
3           available
5           hold

Following is the MySQL query that I am using currently :

SELECT count(id), status FROM `inventory_master` GROUP BY status

Upvotes: 0

Views: 359

Answers (6)

Mitz
Mitz

Reputation: 561

Try this 

Select Case When status='So-Hold' Then 'Hold' Else status End,COUNT(*) 
From TableName
Group by Case When status='So-Hold' Then 'Hold' Else status End

Upvotes: 0

user4420255
user4420255

Reputation:

Two steps. First (Sub-Select replaces 'so-hold' to 'hold') using CASE. Second step GROUP BY.

SELECT g1.status, COUNT(*)
  FROM ( SELECT CASE status
                 WHEN 'so-hold' THEN 'hold'
                 ELSE status
                END AS status,
                id
           FROM test_tw
        ) g1
GROUP BY g1.status
ORDER BY g1.status;

(tested with ORACLE)

Upvotes: 0

potashin
potashin

Reputation: 44591

You can try something like this :

SELECT COUNT(*), status FROM table WHERE status NOT IN('hold', 'so-hold') GROUP BY status
UNION
SELECT COUNT(*), 'hold' FROM table WHERE status IN('hold', 'so-hold')

At first you COUNT normal groups and after that UNION with COUNT for two subgroups hold + so-hold.

SQLFiddle

Upvotes: 0

Jens
Jens

Reputation: 69470

You can use case in this situation:

select status, count(*) from `inventory_master`  group by case status when 'so-hold' then 'hold' else status end;

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64486

You can use CASE statement

select 
count(id),
case when status = 'so-hold'
     then 'hold'
     else status
end as status_col
from inventory_master
group by status_col

DEMO

Upvotes: 1

SMA
SMA

Reputation: 37073

Try replacing "so-" with empty string and then try your query as below:

SELECT newStatus, COUNT(*)
FROM
(SELECT REPLACE(status, 'so-', '') as newStatus
 FROM MYTABLE) AS tab
 GROUP BY newStatus

Upvotes: 0

Related Questions