How to combine 2 same SQL queries

Question

Can you help me with combining 2 similar select sql queries from the same table as shown below:

Query #1

SELECT COUNT(name) 
FROM orders 
WHERE name = '$product_name' 
  AND order_status LIKE '%returned%'

Query #2:

SELECT COUNT(name) 
FROM orders 
WHERE name = '$name_ru' 
  AND order_status LIKE '%returned%'

I have tried to separate "name='$product_name'" and "name='$name_ru'" with "OR" operator, but that showed me both results, I even tried UNION ALL but with no luck, can somebody show me the correct way of accomplishing my task?

Hint: You can use `SUM` on if the condition is X then 1 otherwise 0. Do that 2x, once for each condition you want to count. — Igor, Jun 05 '21 at 10:25
Side note: I am not sure how `'$name_ru'` is being added but should this be string concatenation then you need to change your approach and use parameters. String concatenation leaves code vulnerable to sql injection attacks as well as possible syntax errors if a string contains an escape character like a single quote. — Igor, Jun 05 '21 at 10:34
Thank you for your concern @Igor, but this query is situated on the admin side with well protected environment, so there is less chance of being injected — zeart, Jun 05 '21 at 14:28
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Jun 09 '21 at 14:19

score 0 · Answer 1 · answered Jun 05 '21 at 10:47

If in an excel you can show the desired output then it will easy to answer. As per my understanding you want results side by side. Please change table name and column name based on you table .

    SELECT SUM(ISNULL(Counter1,0)) Counter1,SUM(ISNULL(Counter2,0)) Counter2 from 
(
SELECT CASE WHEN [NAME] LIKE '%a%' THEN COUNT(1) END Counter1,
CASE WHEN [NAME] LIKE '%af%' THEN COUNT(1) END Counter2 
from EMP
GROUP BY Name
)A

Dri372 · Answer 2 · 2021-06-05T16:45:59.637

0

$sql="SELECT
  COUNT(name) filter (WHERE name = '$product_name' AND order_status LIKE '%returned%') AS nb1,
  COUNT(name) filter (WHERE name = '$name_ru' AND order_status LIKE '%returned%') AS nb2
  FROM orders";

work on postgresql

and "less elegant" but work on postgresl, mysql,...

SELECT nb1,nb2 FROM 
  ( SELECT 1 AS id,COUNT(name) AS nb1
FROM orders 
WHERE name = '$product_name' AND order_status LIKE '%returned%'
) r1
  JOIN
  ( SELECT 1 AS id,COUNT(name) AS nb2
FROM orders 
WHERE name = '$name_ru' AND order_status LIKE '%returned%'
) r2 USING (id);

and this one could also work (ok on postgresql , to be tested on mysql)

SELECT nb1,nb2 FROM 
  ( SELECT COUNT(name) AS nb1
FROM orders 
WHERE name = '$product_name' AND order_status LIKE '%returned%'
) r1
  JOIN
  ( SELECT COUNT(name) AS nb2
FROM orders 
WHERE name = '$name_ru' AND order_status LIKE '%returned%'
) r2 ON true;

edited Jun 05 '21 at 16:45

answered Jun 05 '21 at 10:47

Dri372

1,275
3
13

I tried this, but it throws error on mysqli – zeart Jun 05 '21 at 11:05
Ok @zeart I updated my answer for mysql & co, I know better postgresql, maybe there is also an more elegant way with it. – Dri372 Jun 05 '21 at 11:35
Thank you @Dri372, but yet there is another error throwing, as I guess this approach is not suitable for mysqli at all, but I think I will go with old fashioned, not so pretty but stable way, at least it worked for me, I just separated these 2 queries and fetched them separately. Long code, but workful – zeart Jun 05 '21 at 14:33
This error was thrown: Error : 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 'SELECT 1 AS id,COUNT(name) AS nb1 FROM orders WHERE name = 'Hot Wheels 4 �' at line 3 – zeart Jun 05 '21 at 16:30
I don't have your tables so I can't test, but it's just a copy/paste error, 2 words SELECT at the first subquery! I corrected. – Dri372 Jun 05 '21 at 16:45

targhs · Answer 3 · 2021-06-05T13:48:35.707

0

Try this.

SELECT COUNT(CASE WHEN name = '$product_name' and order_status LIKE '%returned%' then 1 else NULL end),
       COUNT(CASE WHEN name = '$name_ru' AND order_status LIKE '%returned%' then 1 else null end)
FROM orders o

edited Jun 05 '21 at 13:48

answered Jun 05 '21 at 11:03

targhs

1,477
2
16
29

Same with this answer, it shows error unfortunately "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 '), COUNT(CASE WHEN name = 'Hot Wheels 4 модели иг' at line 1" – zeart Jun 05 '21 at 11:14
missing "END" clause before count close parenthesis... – DRapp Jun 05 '21 at 11:23
oh yes....its been a long time that i used sql.. Thanks – targhs Jun 05 '21 at 13:48

score 0 · Answer 4 · answered Jun 05 '21 at 11:27

Similar approaches by others, but they are all going against the entire orders table. By doing the count(case) AND applying the where clause, you limit the amount of data being tested against and simplify the case clause too.

SELECT 
      SUM(CASE WHEN name = '$product_name' then 1 else 0 end ) Name1Count,
      SUM(CASE WHEN name = '$name_ru' then 1 else 0 end) Name2Count
   FROM 
      orders o
   where
          -- only consider those with a "%returned%" qualifier
          order_status LIKE '%returned%' 
      -- AND also the two name values you are looking for
      AND name in ('$product_name', '$name_ru' )

Thank you @DRapp, I tried your approach, but with no luck, it did not summarize, so I decided not to bother you guys and instead of combining, I just separated these 2 queries and fetched them separately, note very elegant, but it works. — zeart, Jun 05 '21 at 14:37

score -1 · Answer 5 · answered Jun 05 '21 at 14:43

Thank you all for your answers, but as I see there is no short and elegant way of combining these 2 queries, so I decided to separate them and then fetch one after another like below, and it did the job, but not in a pretty way that I expected:

$query_returned = "SELECT COUNT(name) FROM orders WHERE name='$product_name' AND order_status LIKE '%returned%'";
$data_returned = mysqli_query($conn,$query_returned) or die("Error : ".mysqli_error($conn)); $show_data_returned = mysqli_fetch_array($data_returned);

                $data_returned_cnt = $show_data_returned['COUNT(name)'];
      
                $query_returned_ru = "SELECT COUNT(name) FROM orders WHERE name='$name_ru' AND order_status LIKE '%returned%'";  
                $data_returned_ru = mysqli_query($conn,$query_returned_ru) or die("Error : ".mysqli_error($conn));
                $show_data_returned_ru = mysqli_fetch_array($data_returned_ru);
                
                $data_returned_cnt_ru = $show_data_returned_ru['COUNT(name)'];

echo ($data_returned_cnt + $data_returned_cnt_ru);

How to combine 2 same SQL queries

5 Answers5