MySQL COUNT integer values on UNION SELECT and show as values

Question

I have table that using this query:

SELECT id, channel  
FROM (
    SELECT s.id, c.sort, s.channel 
    FROM streams s, channels c 
    WHERE JSON_SEARCH(s.bouquet, 'one', 1) IS NOT NULL 
    AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL 
    AND JSON_SEARCH(c.sort, 'one', s.id) IS NOT NULL  

    UNION 

    SELECT m.marker, c.sort, m.channel 
    FROM markers m, channels c, streams s 
    WHERE JSON_SEARCH(m.bouquet, 'one', 1) IS NOT NULL 
    AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL
    AND JSON_SEARCH(c.sort, 'one', m.marker) IS NOT NULL) ch 
ORDER BY LOCATE(CONCAT('"', id, '"'), sort);

Return this table:

+----+----------+
| id | channel  |
+----+----------+
| m2 | AAA      |
| 1  | CC_DDD   |
| 2  |  DD_EEE  |
| 11 | FF_GGG   |
| m4 | BBB      |
| m3 | CCC      |
| m5 | MMM      |
| m1 | GGGG     |
+----+----------+

Now i need to count id field but only integer values, so i try to add:

SELECT id, channel, COUNT(id) AS values...

But i get this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values FROM 
(SELECT s.id, c.sort, s.channel FROM streams s, channels c WHERE JS' at line 1

So i need to get this:

+----+----------+--------+
| id | channel  | values |
+----+----------+--------+
| m2 | AAA      |   3    |
| 1  | CC_DDD   |        |
| 2  | DD_EEE   |        |
| 11 | FF_GGG   |        |
| m4 | BBB      |        |
| m3 | CCC      |        |
| m5 | MMM      |        |
| m1 | GGGG     |        |
+----+----------+--------+

So the point is that i need to count only integer values and NOT values that begins with m (m2,m4,m3,m5,m1).

Is this possible with this single query to return count values or do i need to run two query at same time?

`values` is a reserved word. Either use a different alias or put it in backticks. — Barmar, May 16 '19 at 20:03
See https://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql for how to test if a field is a number. — Barmar, May 16 '19 at 20:06
You can use `SUM(id REGEXP '^[0-9]+$')` to count the number of numeric values. — Barmar, May 16 '19 at 20:07
I have problem when i add COUNT to SELECT query, it needs to be in other place but i don't know where? — user2631534, May 16 '19 at 20:21
Why are you adding `COUNT`? You're not counting anything, you need to sum the result of the comparison. — Barmar, May 16 '19 at 20:23
You also need to use `GROUP BY` when you use `COUNT` or `SUM`. — Barmar, May 16 '19 at 20:23
SELECT id, channel, SUM(id REGEXP '^[0-9]+$') AS val FROM (SELECT s.id, c.sort, s.channel FROM streams s, channels c WHERE JSON_SEARCH(s.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.sort, 'one', s.id) IS NOT NULL UNION SELECT m.marker, c.sort, m.channel FROM markers m, channels c, streams s WHERE JSON_SEARCH(m.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.sort, 'one', m.marker) IS NOT NULL) ch GROUP BY id ORDER BY LOCATE(CONCAT('"', id, '"'), sort); — user2631534, May 16 '19 at 20:37
Above query i try but i get error: ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ch.channel' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by — user2631534, May 16 '19 at 20:37
See https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql — Barmar, May 16 '19 at 20:37
SELECT COUNT(id) FROM ( SELECT id, channel FROM (SELECT s.id, c.sort, s.channel FROM streams s, channels c WHERE JSON_SEARCH(s.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.sort, 'one', s.id) IS NOT NULL UNION SELECT m.marker, c.sort, m.channel FROM markers m, channels c, streams s WHERE JSON_SEARCH(m.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.sort, 'one', m.marker) IS NOT NULL) ch ORDER BY LOCATE(CONCAT('"', id, '"'), sort) ) x WHERE id NOT LIKE '%m%'; — user2631534, May 16 '19 at 20:49
Only above works and returns COUNT(id) 3 but i need other fields like in above example..if you could give me clue how to archieve this...Thanks — user2631534, May 16 '19 at 20:50
Don't try to put long code in comments, it's unreadable. Put it in the question so you can format it readably. — Barmar, May 16 '19 at 20:51
Why do you keep writing `count(id)`? You don't want to count all IDs, you just want to count the numeric ones. — Barmar, May 16 '19 at 20:51
Why is the count 3 for the id `m2` when that's not numeric? And why don't any of the other rows have a count? — Barmar, May 16 '19 at 20:54
If you look at first post table numeric value is 1, 2 and 11 so i have 3 numeric values..this is correct...all i im trying now is to return | id | channels | COUNT(id) | using query but i can't it go working...i think i will use two queries..one for return id and channels and other for returning COUNT(id) — user2631534, May 16 '19 at 20:57

score 1 · Accepted Answer · answered May 16 '19 at 21:03

You need to join with a subquery that returns the count you want.

SELECT id, channel, y.count
FROM (
    SELECT s.id, c.sort, s.channel 
    FROM streams s, channels c 
    WHERE JSON_SEARCH(s.bouquet, 'one', 1) IS NOT NULL 
    AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL 
    AND JSON_SEARCH(c.sort, 'one', s.id) IS NOT NULL  

    UNION 

    SELECT m.marker, c.sort, m.channel 
    FROM markers m, channels c, streams s 
    WHERE JSON_SEARCH(m.bouquet, 'one', 1) IS NOT NULL 
    AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL
    AND JSON_SEARCH(c.sort, 'one', m.marker) IS NOT NULL) ch 
CROSS JOIN (
    SELECT SUM(count) AS count
    FROM (
        SELECT SUM(s.id RLIKE '^[0-9]+$') AS count
        FROM streams s, channels c 
        WHERE JSON_SEARCH(s.bouquet, 'one', 1) IS NOT NULL 
        AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL 
        AND JSON_SEARCH(c.sort, 'one', s.id) IS NOT NULL 
        UNION ALL
        SELECT SUM(s.id RLIKE '^[0-9]+$') AS count
        FROM markers m, channels c, streams s 
        WHERE JSON_SEARCH(m.bouquet, 'one', 1) IS NOT NULL 
        AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL
        AND JSON_SEARCH(c.sort, 'one', m.marker) IS NOT NULL) AS x) AS y
ORDER BY LOCATE(CONCAT('"', id, '"'), sort);

This will put the count in every row of the result, not just the first row. You can filter it out in your display code.

Thanks..it works..but i added COUNT not SUM (with SUM i got 23 as y.count, and with COUNT i got 3 wich is correct result)...thanks...big...big vote for correct and detail answer — user2631534, May 17 '19 at 04:49

MySQL COUNT integer values on UNION SELECT and show as values

1 Answers1