-1

I have a mysql query

SELECT SUM(idHit = 122791), SUM(idHit = 950521) FROM hits

that returns two values, instead of only one row with two columns:

5, 7

Using mysql syntax, I would like to get both values in one column with two rows? Like

5
7

That´s all, I suppose is necessary to convert sql query to subquery, but not sure. Please HELP.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Alejandro
  • 1
  • If your issue involves just two results, you could try running select of the first **UNION** select of the second. If, on the other hand, this is just an example and you may have several results (say, 3 or more), you need to **PIVOT** your result. You may find this post useful: https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns. – FDavidov Jan 15 '18 at 13:05

2 Answers2

1

Use GROUP BY:

SELECT idHit, COUNT(*)
FROM t
WHERE idHit IN (122791, 950521)
GROUP BY idHit;

If you have a problem where both may not exist, then I would suggest left join:

select ids.idhit, count(t.idhit)
from (select 122791 as idhit union all select 950521) ids left join
     t
     on t.idhit = ids.idhit
group by ids.idhit;

The union all is an interesting approach. I would phrase it as:

SELECT 122791, COUNT(*) FROM hits WHERE idHit = 122791
UNION ALL
SELECT 950521, COUNT(*) FROM hits WHERE idHit = 950521;

Note only is this standard SQL, but it can make use of an index if one is available.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Don´t works. 1. Return only one value because idHit 950521 don´t exist in table HITS. 2. I need to maintain INTACT the original query: SELECT SUM(idHit = 122791), SUM(idHit = 950521) FROM hits , but is posible to use results in a subquery – Alejandro Jan 15 '18 at 13:20
0

You can do it with UNION 

SELECT SUM(idHit = 122791) FROM hits
UNION ALL
SELECT SUM(idHit = 950521) FROM hits
isaace
  • 3,336
  • 1
  • 9
  • 22
  • Yes, I know, but it generates two querys, so it is less efficient, meanwhile, """ SELECT SUM(idHit = 122791), SUM(idHit = 950521) FROM hits """ is an efficient UNIQUE QUERY. Please, reduce the problem to convert the inicial results "5, 7" in only one row, to 5 AND 7 in two separated rows. I need efficient because the real query return thousands of values. Thank you very very much. – Alejandro Jan 15 '18 at 14:07