CODEWITHSUNDEEP
sqlsql-serversql-server-2008reporting-services
fiddy
fiddy

Reputation: 35

Using conditions to specify groups for GROUP BY

I'm not even sure if this is possible using SQL, but I'm completely stuck on this problem. I have a table like this: 

Total Code
212  XXX_09_JUN
315  XXX_7_JUN
68   XXX_09_APR
140  XXX_AT_APR
729  XXX_AT_MAY

I need to sum the "total" column grouped by the code. The issue is that "XXX_09_JUN" and "XXX_7_JUN" and "XXX_09_APR" need to be the same group.

I was able to accomplish this by creating a new column where I assigned values based on the row's code, but since this needs to be done on multiple tables with millions of entries, I can't use that method.

Is there some way that I could group the rows based on a condition such as:

WHERE Code LIKE '%_09_%' OR Code LIKE '%_7_%'

This is not the only condition - I need about 10 conditions like this. Sorry if that doesn't make sense, I'm not sure how to explain this...

Also, if this can be accomplished using Visual Studio 2008 and SSRS more easily, that would work as well because that is the final goal of this query.

Edit: To clarify, this would be the ideal result:

Total Code
595  had_a_number
869  had_at

Upvotes: 0

Views: 79

Answers (3)

craM 4D-61-72-63
craM 4D-61-72-63

Reputation: 43

Heres a ridiculous way to solve your problem:

Schema:

CREATE TABLE tblTotalCode(
  Total INTEGER,
  Code VARCHAR(15)
)

INSERT INTO tblTotalCode VALUES(212,'XXX_09_JUN')
                              ,(315,'XXX_7_JUN')
                              ,(68, 'XXX_09_APR')
                              ,(140,'XXX_AT_APR')
                              ,(729,'XXX_AT_MAY')

Code:

SELECT CASE WHEN(LEFT(RIGHT(Code, LEN(Code) - CHARINDEX('_', Code, 1)), CHARINDEX('_', RIGHT(Code, LEN(Code) - CHARINDEX('_', Code, 1)), 1) - 1)) IN ('09','7') THEN '09 or 7' ELSE 'Not 09 or 7' END AS '09 or 7 Group'
, SUM(Total) AS 'Total'
FROM tblTotalCode
Group By CASE WHEN(LEFT(RIGHT(Code, LEN(Code) - CHARINDEX('_', Code, 1)), CHARINDEX('_', RIGHT(Code, LEN(Code) - CHARINDEX('_', Code, 1)), 1) - 1)) IN ('09','7') THEN '09 or 7' ELSE 'Not 09 or 7' END

Upvotes: 0

spencer7593
spencer7593

Reputation: 108400

One option is to use a CASE expression:

 GROUP BY CASE
            WHEN Code LIKE '%!_09!_%' ESCAPE '!'
               THEN 'had_a_number'
            WHEN Code LIKE '%!_7!_%' ESCAPE '!' 
               THEN 'had_a_number'
            WHEN Code LIKE '%!_AT!_%' ESCAPE '!'
              THEN 'had_at'
            ELSE 'other'
          END

Add however many WHEN conditions to assign whatever condition to a "group".

Note that the underscore is a wildcard character for the LIKE operator. An underscore will match any single character. To search for a literal underscore, you would need to "escape" the underscore within the string literal.

  'A_12_E' LIKE '%_12_%'               => TRUE
  'AB12DE' LIKE '%_12_%'               => TRUE

  'A_12_E' LIKE '%!_12!_%' ESCAPE '!'  => TRUE
  'AB12DE' LIKE '%!_12!_%' ESCAPE '!'  => FALSE

Upvotes: 3

M.Ali
M.Ali

Reputation: 69524

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE TEST_TABLE(Total INT, Code VARCHAR(20))
GO

INSERT INTO TEST_TABLE  VALUES 
(212, 'XXX_09_JUN'),
(315, 'XXX_7_JUN'),
(68, 'XXX_09_APR'),
(140, 'XXX_AT_APR'),
(729, 'XXX_AT_MAY')
GO

Query 1:

SELECT SUM(Total) Total
      ,CASE 
             WHEN Code LIKE '%_%[0-9]%_%' 
               THEN 'had a number' 
            WHEN Code NOT LIKE '%_%[0-9]%_%' 
               THEN 'had at'
       END AS Code

FROM TEST_TABLE
GROUP BY CASE 
             WHEN Code LIKE '%_%[0-9]%_%' 
               THEN 'had a number' 
            WHEN Code NOT LIKE '%_%[0-9]%_%' 
               THEN 'had at'
       END

Results:

| TOTAL |         CODE |
|-------|--------------|
|   595 | had a number |
|   869 |       had at |

Upvotes: 1

Related Questions