-2

I'm trying to build a T-SQL query to list some columns and group values with certain criteria under a different name.

This is what I have in my table: 

Category   | Verdict      | Requests
-----------|--------------|----------
Category1  | Allowed      | 1000
Category2  | denied_now   | 100
Category2  | denied_later | 101
Category3  | Allowed      | 203

I wanted to end up with a result like this. 

Category   | Verdict      | Requests
-----------|--------------|----------
Category1  | Allowed      | 1000
Category2  | denied_all   | 201
Category3  | Allowed      | 203

I tried a SELECT combined with GROUP BY and HAVING but I always get an error. How to combine those values into a new row under a new name for Verdict = denied% ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Frosa
  • 37
  • 1
  • 1
  • 9
  • 1
    what is your logic? How come Category 4 come into picture suddenly? What is your try? What is your Error? – techspider Sep 02 '16 at 16:49
  • Please, post your attempt. From your posted table and results it's impossible to know what you need. It looks like if it's a simple `select *` with a `where` condition, so you must be missing something, besides what techspider commented. – Andrew Sep 02 '16 at 16:51

1 Answers1

1

I think this should do the trick:

SELECT Category, CASE WHEN Verdict LIKE 'denied%' THEN 'denied_all' ELSE Verdict END AS Verdict, SUM(Requests) Requests
FROM TableName
GROUP BY Category, CASE WHEN Verdict LIKE 'denied%' THEN 'denied_all' ELSE Verdict END
ORDER BY Category

I don't see you need any condition in the HAVING.

So what I'm doing here is simply replacing all 'denied%' verdicts with 'denied_all' so they can be grouped, then I group by Category and modified Verdict, and finally sum all values in the Request field. No big secrets.

If you don't want to repeat the CASE WHEN, I think you have to go with either of these:

SELECT Category, Verdict, SUM(Requests) Requests
FROM (
    SELECT Category, CASE WHEN Verdict LIKE 'denied%' THEN 'denied_all' ELSE Verdict END AS Verdict, Requests
    FROM TableName
) SubQ
GROUP BY Category, Verdict
ORDER BY Category

Or:

;WITH SubQ AS
(
    SELECT Category, CASE WHEN Verdict LIKE 'denied%' THEN 'denied_all' ELSE Verdict END AS Verdict, Requests
    FROM TableName
)

SELECT Category, Verdict, SUM(Requests) Requests
FROM SubQ
GROUP BY Category, Verdict
ORDER BY Category
Andrew
  • 7,602
  • 2
  • 34
  • 42
  • not explained is why this works. GROUP BY smashes the records that exist, and since the SELECT statement occurs AFTER the group by, altering the values in the SELECT statement will not work (why this is duplicated). Since SQL is comparing the values it is smashing, another if this then that logic will not break the code. – clifton_h Sep 03 '16 at 01:51
  • Sorry, I don't understand you. The original query had one small error as I typed it without any test but now is fixed, and it's working perfectly. As long as what's selected matches what's grouped, it will work, regardless of if it was modified. – Andrew Sep 03 '16 at 05:24
  • actually, I was commenting in mind for the OP. Your query is great. :) – clifton_h Sep 03 '16 at 06:45