CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Diego
Diego

Reputation: 36156

How do I group by generic field?

If I have a table with a status field and I want to know how many records of each status I can do a simple group by. And how about if I want to know the count for 2 records and the count for all the others.

In other words I want this:

Status   Count
-------- -----
Success   X
Running   Y
Failure   Z

but Failure is not Failure on the table, it contains the actual error message, so I want everything that's different that Success and Running

Upvotes: 1

Views: 91

Answers (3)

Royi Namir
Royi Namir

Reputation: 148624

SELECT DISTINCT CASE 
                     WHEN [status]='s' OR [STATUS]='r' THEN [status]
                     ELSE 'OTHER'
                END AS STATUS
      ,COUNT(1) OVER(
           PARTITION BY CASE 
                             WHEN [status]='s'
           OR [STATUS]='r' THEN [status] ELSE 'aaa' END
       ) AS 'count'
FROM   tbl2

enter image description here

Upvotes: 1

user756519
user756519

Reputation:

Click here to view the demo in SQL Fiddle.

Script:

CREATE TABLE errormsgs 
(
    id          INT         NOT NULL IDENTITY
  , statusmsg   VARCHAR(30) NOT NULL
);

INSERT INTO errormsgs (statusmsg) VALUES
  ('Success'),
  ('This is error message 1.'),
  ('Running'),
  ('This is error message 2.'),
  ('This is error message 3.'),
  ('Success'),
  ('Success'),
  ('This is error message 4.'),
  ('Running'),
  ('failure, may be'),
  ('failure, absolutely.');

;WITH statuses AS
(
    SELECT  CASE
                WHEN statusmsg NOT IN ('Success', 'Running') THEN 'Failure' 
                ELSE statusmsg 
            END status
    FROM    errormsgs
)
SELECT      status
        ,   COUNT(status) AS status_count
FROM        statuses 
GROUP BY    status;

Output:

STATUS   STATUS_COUNT
-------- ------------
Failure        6
Running        2
Success        3

Upvotes: 3

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

select case when Status <> 'Success' 
             and Status <> 'Running' 
            then 'Failure' 
            else Status 
        end Status, 
       count (*) [Count]
from atable
group by case when Status <> 'Success' 
               and Status <> 'Running' 
              then 'Failure' 
              else Status 
          end

Upvotes: 3

Related Questions