CODEWITHSUNDEEP
sql-server
Andrei Sevtsenko
Andrei Sevtsenko

Reputation: 223

combining rows for duplicate values

I'm currently trying to count how many times an article's category appears in a ticket table.
The problem is that the categories have sub-categories and they're all listed as a single string in the table ("Cat1 > Subcat1", "Cat1 > Subcat2", etc).

What I've done so far is managed to get all the distinct categories listed, but I want to strip any subcategories from the results and combine any duplicates.

As an example, I have the following categories and counts:

Category | 13
Category > Subcat1 | 14
Category > Subcat2 | 3

What I want to see is:

Category | 30

So far I've managed to strip the subcategories, but the actual row is basically duplicated with different values:

Category | 13
Category | 14 Category | 3

Here is my current code:

SELECT 
CASE
    WHEN CHARINDEX(' >',SUBSTRING(KM2.GENERATEDCATS,16,20))=0 
    THEN SUBSTRING(KM2.GENERATEDCATS,16,20) 
    ELSE SUBSTRING(SUBSTRING(KM2.GENERATEDCATS,16,20),1,CHARINDEX(' >',SUBSTRING(KM2.GENERATEDCATS,16,20)))
END AS "Final",
COUNT(KM2.GENERATEDCATS) AS "Count"

FROM INCIDENTSM1 AS SD LEFT JOIN KMDOCUMENTM1 AS KM1 ON SD.CBA_KPF_ID=KM1.ID
LEFT JOIN KMDOCUMENTA2 AS KM2 ON KM1.ID=KM2.ID
GROUP BY KM2.GENERATEDCATS
ORDER BY Count DESC

Legend:

INCIDENTSM1 - Ticket table
KMDOCUMENTM1 - Article table
KMDOCUMENTA2 - Categories (separate from main table)
GENERATEDCATS - Category column

Is it possible to combine all those 'Category' rows into one with a total count?

Upvotes: 2

Views: 43

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try to GROUP BY the extracted category name:

SELECT CASE
          WHEN CHARINDEX(' >',SUBSTRING(KM2.GENERATEDCATS,16,20))=0 
          THEN SUBSTRING(KM2.GENERATEDCATS,16,20) 
          ELSE SUBSTRING(SUBSTRING(KM2.GENERATEDCATS,16,20),1,
                         CHARINDEX(' >',SUBSTRING(KM2.GENERATEDCATS,16,20)))
       END AS "Final",
COUNT(KM2.GENERATEDCATS) AS "Count"    
FROM INCIDENTSM1 AS SD 
LEFT JOIN KMDOCUMENTM1 AS KM1 ON SD.CBA_KPF_ID=KM1.ID
LEFT JOIN KMDOCUMENTA2 AS KM2 ON KM1.ID=KM2.ID
GROUP BY CASE
            WHEN CHARINDEX(' >',SUBSTRING(KM2.GENERATEDCATS,16,20))=0 
            THEN SUBSTRING(KM2.GENERATEDCATS,16,20) 
            ELSE SUBSTRING(SUBSTRING(KM2.GENERATEDCATS,16,20),1,CHARINDEX(' >',
                           SUBSTRING(KM2.GENERATEDCATS,16,20)))
         END
ORDER BY Count DESC

Upvotes: 3

Related Questions