Count case expression with wildcards

Question

Suppose I have this query:

SELECT
(CASE
  WHEN troublequeue LIKE '%DL%' THEN 'Distribution Line Crew'
  WHEN troublequeue LIKE '%MS%' THEN 'Service Crew' 
  WHEN troublequeue LIKE '%TM%' THEN 'Troubleman'
  WHEN troublequeue LIKE '%TT%' THEN 'Tree Crew'
  WHEN troublequeue LIKE '%POLE%' THEN 'Pole Job'
  WHEN troublequeue LIKE '%XFMR%' THEN 'Xfmr Job'
  WHEN troublequeue LIKE '%TOC%' THEN 'Tennesee One Call'
  ELSE 'Other' END) as HeldJobType, 
COUNT(eventid)
FROM
  electric_jobs
WHERE
  BeginDateTime BETWEEN TO_DATE('<{[begin_date]}>', 'MM/DD/YYYY') AND TO_DATE('<{[end_date]}>', 'MM/DD/YYYY')
  AND troublequeue IS NOT NULL
GROUP BY
(CASE
  WHEN troublequeue LIKE '%DL%' THEN 'Distribution Line Crew'
  WHEN troublequeue LIKE '%MS%' THEN 'Service Crew' 
  WHEN troublequeue LIKE '%TM%' THEN 'Troubleman'
  WHEN troublequeue LIKE '%TT%' THEN 'Tree Crew'
  WHEN troublequeue LIKE '%POLE%' THEN 'Pole Job'
  WHEN troublequeue LIKE '%XFMR%' THEN 'Xfmr Job'
  WHEN troublequeue LIKE '%TOC%' THEN 'Tennesee One Call'
  ELSE 'Other' END)
ORDER BY
(CASE
  WHEN troublequeue LIKE '%DL%' THEN 'Distribution Line Crew'
  WHEN troublequeue LIKE '%MS%' THEN 'Service Crew' 
  WHEN troublequeue LIKE '%TM%' THEN 'Troubleman'
  WHEN troublequeue LIKE '%TT%' THEN 'Tree Crew'
  WHEN troublequeue LIKE '%POLE%' THEN 'Pole Job'
  WHEN troublequeue LIKE '%XFMR%' THEN 'Xfmr Job'
  WHEN troublequeue LIKE '%TOC%' THEN 'Tennesee One Call'
  ELSE 'Other' END)

but when I run the query, all of the fields that have multiple inputs get returned as DL. How can I get SQL to give me a count of each time one of these occur, even if there are multiple inputs in one field? The issues come when there is a field with a comma and multiple inputs.

Sample data example is

Troublequeue <- column name
1 TM
2 DL
3 DL
4 DL
5 AMI/STPR
6 PM
7 PM,CR
8 DL
9 TM
10 AMI/STPR
11 TM
12 AMI/STPR
13 AMI/STPR
14 PM
15 AMI/STPR
16 PM
17 MS
18 TOCY, TCN, TT, DL, POLE

[Edit] the question and provide a [example], i.e. the `CREATE` statements of the tables (paste the **text**, don't use images), `INSERT` statements for sample data (dito) and the desired result with that sample data in tabular text format. — sticky bit, Mar 26 '21 at 12:24
@HoneyBadger no this is a database I am running this against. I have this query in an html report that I post online, but I am wanting it to count every instance one of those codes pops up, as a field can have more than one input in it. like so [1]: https://i.stack.imgur.com/Lwwos.png — collin7681, Mar 26 '21 at 12:28
Again: **[Edit]** the question to add information. Don't hide it in comments. But don't use images for tables. Use `CREATE` and `INSERT` statements. — sticky bit, Mar 26 '21 at 12:32
Fix your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes it is.). — sticky bit, Mar 26 '21 at 12:32
@stickybit I wish I could fix the schema, but this is how oracle handed us the databse. I cannot edit anything in that databse. This is for my company so I don't have those permissions. So based on that article, it seems like I am screwed trying to count this stuff with the delimiter in there... — collin7681, Mar 26 '21 at 12:50
If you cannot fix your design, you'll have to split the values so they are atomic (as they should be in the first place) — HoneyBadger, Mar 26 '21 at 12:51
@honeybadger hmmm, I am not so sure I would know how to do that my friend. What would be the best way to go about this? — collin7681, Mar 26 '21 at 12:54

score 0 · Answer 1 · answered Mar 26 '21 at 12:22

0

If I understand correctly, you want count(distinct):

count(distinct troublequeue)

This will return the number of distinct values for each row.

You might also want:

min(troublequeue), max(troublequeue)

to get examples of values.

Note that many databases would allow you to use:

group by HeldJobType

All databases should allow:

order by HeldJobType;

answered Mar 26 '21 at 12:22

Gordon Linoff

1,242,037
58
646
786

2

@collin7681 please add sample data to your question (not as image, as formatted text) – HoneyBadger Mar 26 '21 at 12:27
1

@collin7681 . . . Explanations belong in the *question*, not in comments. Please edit your question. – Gordon Linoff Mar 26 '21 at 12:35
@HoneyBadger sorry just made my account this morning lol and am unfamiliar with the standards on here – collin7681 Mar 26 '21 at 12:38
@GordonLinoff sorry man I am new to this. Just made my account today. I have added some sample data for clarification! – collin7681 Mar 26 '21 at 12:43

MT0 · Accepted Answer · 2021-03-26T13:22:53.043

You can use:

WITH trouble_ids ( id, HeldJobType ) AS (
  SELECT 'DL', 'Distribution Line Crew' FROM DUAL UNION ALL
  SELECT 'MS', 'Service Crew' FROM DUAL UNION ALL
  SELECT 'TM', 'Troubleman' FROM DUAL UNION ALL
  SELECT 'TT', 'Tree Crew' FROM DUAL UNION ALL
  SELECT 'POLE', 'Pole Job' FROM DUAL UNION ALL
  SELECT 'XFMR', 'Xfmr Job' FROM DUAL UNION ALL
  SELECT 'TOC', 'Tennesee One Call' FROM DUAL
),
job_ids ( event_id, troublequeue, job_id, lvl, max_lvl ) AS (
  SELECT event_id,
         troublequeue,
         REGEXP_SUBSTR( troublequeue, '[A-Z]+', 1, 1, 'i' ),
         1,
         REGEXP_COUNT( troublequeue, '[A-Z]+', 1, 'i' )
  FROM   electric_jobs
  WHERE  BeginDateTime BETWEEN DATE '2021-01-01' AND DATE '2021-02-01'
UNION ALL
  SELECT event_id,
         troublequeue,
         REGEXP_SUBSTR( troublequeue, '[A-Z]+', 1, lvl + 1, 'i' ),
         lvl + 1,
         max_lvl
  FROM   job_ids
  WHERE  lvl < max_lvl
)
SELECT COALESCE( t.HeldJobType, 'Other' ) AS HeldJobType,
       COUNT( e.event_id )
FROM   job_ids e
       LEFT OUTER JOIN trouble_ids t
       ON ( e.job_id = t.id )
GROUP BY
       COALESCE( t.HeldJobType, 'Other' );

Which, for your sample data:

CREATE TABLE electric_jobs ( event_id, troublequeue ) AS
SELECT  1, 'TM' FROM DUAL UNION ALL
SELECT  2, 'DL' FROM DUAL UNION ALL
SELECT  3, 'DL' FROM DUAL UNION ALL
SELECT  4, 'DL' FROM DUAL UNION ALL
SELECT  5, 'AMI/STPR' FROM DUAL UNION ALL
SELECT  6, 'PM' FROM DUAL UNION ALL
SELECT  7, 'PM,CR' FROM DUAL UNION ALL
SELECT  8, 'DL' FROM DUAL UNION ALL
SELECT  9, 'TM' FROM DUAL UNION ALL
SELECT 10, 'AMI/STPR' FROM DUAL UNION ALL
SELECT 11, 'TM' FROM DUAL UNION ALL
SELECT 12, 'AMI/STPR' FROM DUAL UNION ALL
SELECT 13, 'AMI/STPR' FROM DUAL UNION ALL
SELECT 14, 'PM' FROM DUAL UNION ALL
SELECT 15, 'AMI/STPR' FROM DUAL UNION ALL
SELECT 16, 'PM' FROM DUAL UNION ALL
SELECT 17, 'MS' FROM DUAL UNION ALL
SELECT 18, 'TOCY, TCN, TT, DL, POLE' FROM DUAL;

Outputs:

HELDJOBTYPE            | COUNT(E.EVENT_ID)
:--------------------- | ----------------:
Distribution Line Crew |                 5
Pole Job               |                 1
Other                  |                17
Tree Crew              |                 1
Service Crew           |                 1
Troubleman             |                 3

db<>fiddle here

@MTO So, what you are thinking is, if I input your first query in place of my original query (but I need to keep the date field and user variables in the date field), that should return the results for each instance, even if the field is delimited? Sorry a bit newer to SQL use and thanks for the help! — collin7681, Mar 26 '21 at 13:05
@collin7681 Updated to add a `WHERE` filter on date and to count all the job ids. — MT0, Mar 26 '21 at 13:23
@MTO one last question. If I added a select 'TOC%' to return anything like TOC, would that mess up the rest of the query? I ask that because those results can be TOCY, TOCN. Lastly, I just want to count the events where the trouble queue is not null from the DB and it seems like the 'Other' category is counting them all events, including nulls. ``` — collin7681, Mar 26 '21 at 13:35
@MTO Thank you so much for your help on this. I got exactly what I needed after playing with it a bit. You're the bomb!!!! — collin7681, Mar 26 '21 at 14:18
@collin7681 If you want to use `TOC%` rather than `TOC` then [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=9bae61aaf10296eb065c2dad7ff7210f). The `Other` category is counting all the values that you do not match (so `AMP`, `STPR`, `TOCY`, `PM` and `TCN`). — MT0, Mar 26 '21 at 14:23
@MTO if I could hug you I would. You've been an absolute life saver with this. I appreciate your help! — collin7681, Mar 26 '21 at 14:36

Count case expression with wildcards

2 Answers2