CODEWITHSUNDEEP
sqlpostgresql
JustCasual
JustCasual

Reputation: 39

Count of Category with LIKE in HAVING?

I'm not sure how to go about this, but I can't seem to make this work. Here's the sample table:

+-----------+---------------+-------+--------------+
| CompanyID |  CompanyName  | Units | Municipality |
+-----------+---------------+-------+--------------+
|       123 | Coconuts Inc. |     1 | Kombu        |
|       234 | Bubble Inc.   |    10 | Dashi        |
|       456 | NS Peel       |    15 | Dashi NY     |
|       789 | Ironbark      |    23 | Dashi NY     |
|       567 | Dr. Balanced  |    12 | Oxford       |
+-----------+---------------+-------+--------------+

I'm trying to achieve this output:

+--------------+---------------+------------+
| Municipality | Company Count | Unit Count |
+--------------+---------------+------------+
| Kombu        |             1 |          1 |
| Oxford       |             1 |         12 |
| Dashi        |             3 |         48 |
+--------------+---------------+------------+

Is it possible to use LIKE functions in the HAVING clause? I tried that and it didn't turn out so great.

Upvotes: 0

Views: 75

Answers (3)

avk
avk

Reputation: 871

If you want Dashi and Dashi NY grouped separately, it would be something like:

SELECT municipality
    , COUNT(1) as company_count
    , SUM(units) as unit_count
FROM your_table
GROUP BY municipality

If Dashi and Dashi NY belong together, you either have another table that connects Dashi and Dashi NY to a particular id, or you use substr() to only group by the first 5 or 6 characters.

Upvotes: 0

jpw
jpw

Reputation: 44891

If you just want to keep the part of the Municipality name that occurs before a space then this query would give you the desired result, but be aware that dealing with this kind of problem (mapping common names) in this way is not very good solution. It would be much better to have a table with mappings between common names and variants.

Edit: I used T-SQL syntax by accident as I failed to notice the Postgresql tag... here's a query that works with Postgresql using a regular expression to extract the first word:

select 
  substring(municipality from E'\\w+\s?') as "Municipality",
  count(distinct CompanyName) as "Company Count", 
  sum(units) as "Unit Count"
from table1
group by substring(municipality from E'\\w+\s?');

Sample SQL Fiddle for Postgresql

And the original T-SQL version:

select 
  case 
    when charindex(' ', Municipality) = 0 
    then Municipality
    else substring(Municipality, 0,charindex(' ', Municipality)) 
  end as Municipality,
  count(distinct [CompanyName]) as [Company Count], 
  sum(units) as [Unit Count]
from table1
group by 
  case 
    when charindex(' ', Municipality) = 0 
    then Municipality
    else substring(Municipality, 0,charindex(' ', Municipality)) 
end

Sample SQL Fiddle (for T-SQL)

A mapping table could look like:

key   alt_name
---   --------
Dashi Dashi NY

Using a table like that you could use a left join to match the names.

Upvotes: 2

shawnt00
shawnt00

Reputation: 17915

This doesn't work so well in a general case, but it's sufficient for an adhoc query and doesn't require any hassle with string parsing functions:

select
    case
        when Municipality like 'Dashi%' then 'Dashi'
        else Municipality
    end, ...
...
group by
    case
        when Municipality like 'Dashi%' then 'Dashi'
        else Municipality
    end

Upvotes: 0

Related Questions