How to merge 2 COUNT rows as a new row in SQL Server?

5vf7fwbs 于 8个月前发布在 SQL Server

关注(0)|答案(2)|浏览(75)

I want to merge 2 rows as a new row in the COUNT result.

This is my current SQL code,

SELECT assignment_status, COUNT(*) AS amount
FROM table_jobs
GROUP BY assignment_status

Here is the current result:

What I'd like it to be is

done: 8
started: 1
pending: 3 //*merged from to_fix and to_validate
todo: 2

Appreciate every help.

sql-server

来源：https://stackoverflow.com/questions/77332879/how-to-merge-2-count-rows-as-a-new-row-in-sql-server

2条答案

按热度按时间

3npbholx1#

You need to use conditional aggregation.

Untested without any consumable sample data, however something like this:

select Status, Count(*) as amount
from table_jobs
cross apply(
  values(Iif(assignment_status in ('to_fix', 'to_validate'), 'Pending', assignment_status))
)s(Status)
group by Status;

赞(0）回复(0）举报 8个月前

a0x5cqrl2#

Stu's answer is probably the right one. Here are a few alternative ways to do it:

-- Write conditional expression directly in group by and select list
-- Downside, duplicates that big expression. Upside, succinct? maybe?
select
    Assignment_status = iif(assignment_status in ('to_fix', 'to_validate'), 'pending', assignment_status),
    amount = count(1)
from table_jobs
group by iif(assignment_status in ('to_fix', 'to_validate'), 'pending', assignment_status)

-- Write conditional expression as a subquery
-- Downside, less succint, upside, only writes the expression once
select
    assignment_status,
    amount = count(1)
from
(
    select
        Assignment_status = iif(assignment_status in ('to_fix', 'to_validate'), 'pending', assignment_status),
        *
    select table_jobs
) a
group by assignment_status

赞(0）回复(0）举报 8个月前

我来回答

How to merge 2 COUNT rows as a new row in SQL Server?

2条答案

相关问题

热门标签

最新问答