Search code examples
mysqlsqlsqlitegroup-bysubstr

SQLite Group By giving duplicates

I am trying to write a query using SQLite but keeping running into the issue where my GROUP BY has duplicates. I am using SQLite and am struggling to get clean results.

I received a file that has a business unit but is displayed as AMER - xyz. I only want the AMER so I used the SUBSTR function. I believe this is where I am encountering my issue.

Below is my query along with sample results:

SELECT DISTINCT(UPPER(SUBSTR(business_unit,1,INSTR(business_unit,' -')))) as business_unit,
    COUNT(*)

FROM leads_market_region_marketo

GROUP BY business_unit

--------------------------------

bu      count
AMER    271107
AMER    130172
AMER    3218
AMER    13072
AMER    1088247
AMER    369576
ANZ     29913

What I want:

bu      count
AMER    x
ANZ     y
EMEA    z
Solution

  • You should GROUP BY the same criteria you want to get your groups.

    SELECT UPPER(SUBSTR(business_unit,1,INSTR(business_unit,' -'))) as business_unit, COUNT(*) 
FROM leads_market_region_marketo 
GROUP BY UPPER(SUBSTR(business_unit,1,INSTR(business_unit,' -'))))

    Hope it helps!