CODEWITHSUNDEEP
mysqlsqlcount
expo101
expo101

Reputation: 15

Count on various substrings from same column

I want to count the number of occurences of several substrings from the same column and with the same WHERE condition. For each substring I have a UNION SELECT statement including the repeating WHERE condition. The final result is a query with appox. 2500 lines of sql code, but it gives good result.

Simplified example of query:

SELECT ‘be’ AS country, count(destination) FROM demo
WHERE destination LIKE ‘%be%’ AND field_a=xzx AND field_b=bbb

UNION SELECT ‘de’ AS country, count(destination) FROM demo
WHERE destination LIKE ‘%de%’ AND field_a=xzx AND field_b=bbb

UNION SELECT ‘fr’ AS country, count(destination) FROM demo
WHERE destination LIKE ‘%fr%’ AND field_a=xzx AND field_b=bbb

UNION SELECT ‘nl’ AS country, count(destination) FROM demo
WHERE destination LIKE ‘%nl%’ AND field_a=xzx AND field_b=bbb

Is it possible to modify the query in such a way that the WHERE condition only appears once in the query?

Please find a simplified example of my question via this link:

https://docs.google.com/document/d/1otjZZlBy6au5E2I7T6NdSYmLayhNGWyXGxGo3gBx_-w/edit

Upvotes: 1

Views: 111

Answers (3)

Joe G Joseph
Joe G Joseph

Reputation: 24116

select a.country,count(*) from
(
select 'be' as country
union all
select 'de' as country
union all
select 'fr' as country
union all
select 'nl' as country
)a join demo d
on d.destination like '%'+a.country+'%'
AND d.field_a=xzx AND d.field_b=bbb
group by a.country

I am not sure whether mySQL supports derived table or not . If its not supported the you have to create a temp table with all different country values and replace it with table a in the above query

Upvotes: 1

MatBailie
MatBailie

Reputation: 86798

DON'T store comma separated values in a single field. Instead, use a many:many relationship table and store one (id,country) combination per row. Your data structure is a SQL-Anti-Pattern and goes against all relational database design principles.

CREATE TABLE map (
  demo_id INT,
  country VARCHAR(2),
  PRIMARY KEY (demo_id, country)
)

INSERT INTO map VALUES
  (1, 'nl'), (1, 'de'), (1, 'be'), (1, 'fr'),
  (2, 'de'), (2, 'fr'), (2, 'be'),
  (3, 'fr'), (3, 'nl'),
  (4, 'nl')

Then you will have this single query...

SELECT
  map.country,
  COUNT(*)
FROM
  demo
INNER JOIN
  map
    ON map.demo_id = demo.id
WHERE
      demo.field_a = 'xzx'
  AND demo.field_b = 'bbb'

Upvotes: 1

BMN
BMN

Reputation: 8508

Use COUNT CASE WHEN ... THEN ... END

SELECT 
COUNT(CASE WHEN destination LIKE "%be%" THEN 1 ELSE NULL END) AS "BE",
COUNT(CASE WHEN destination LIKE "%fr%" THEN 1 ELSE NULL END) AS "FR",
COUNT(CASE WHEN destination LIKE "%de%" THEN 1 ELSE NULL END) AS "DE",
COUNT(CASE WHEN destination LIKE "%nl%" THEN 1 ELSE NULL END) AS "NL"
FROM demo
WHERE field_a = "xzx" AND field_b = "bbb"

But you should really think about changing the structure of your table. Having comma-separated values in a single field is not really recommanded.

Instead, you should have for example here an ID for each travel (or whatever it is, I said travel because of the destination field). Then, make an entry for each destination within this travel. With this kind of structure, you'd have a request like this :

SELECT destination, COUNT(id) 
FROM demo 
WHERE field_a = "xzx" AND field_b = "bbb" 
GROUP BY destination

Upvotes: 0

Related Questions