Count same values in mysql query

Question

So i have a query like

SELECT * FROM `catalog` WHERE `id` IN ('2','2','3','3','3');

And this return only 2 rows with id 2 and 3. It is possible make it return 5 rows (2 with id "2" and 3 with id "3") or add count as new column?

You're using a database engine to retrieve redundant data, for god sakes why? — nyson
– nyson, Commented Oct 27, 2012 at 22:14
This is more than a little strange. ID is normally the primary key of a table, which means that you only have one. How do you expect to get multiple and why would you want to? Secondly, you're quoting numbers. Please don't do that unless they're stored in a character data-type. Please don't do that. — Ben
– Ben, Commented Oct 27, 2012 at 22:30
@Ben Yes, ID is primary. I need to get all rows with ID from array, what look like "2,2,3,3,3", and add count of same ID in this array. — droptheplot
– droptheplot, Commented Oct 27, 2012 at 22:42

AW101 · Accepted Answer · 2012-10-27 23:04:58Z

1

Not sure why you would want to do something like this, but instead of using an 'in' clause you could use an inner query:

select *
from `catalog` c,
(
    select 2 ids
    union all
    select 2
    union all
    select 3
    union all
    select 3
    union all
    select 3
) k
where c.id = k.ids

answered Oct 27, 2012 at 23:04

AW101

1,65015 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Duccio Fabbri · Accepted Answer · 2012-10-27 23:13:09Z

1

Try something like this:

SELECT t.p,count(*)  FROM 
    catalog, 
    (SELECT 2 as id
    Union all select 2 as id
    Union all select 3 as id
    Union all select 3 as id
    Union all select 3 as id)as t 
where catalog.id = t.id

answered Oct 27, 2012 at 23:13

Duccio Fabbri

1,0181 gold badge11 silver badges21 bronze badges

3 Comments

Ben Over a year ago

This is identical to AW101's answer

Duccio Fabbri Over a year ago

Yes, it's true, while I was testing my solution, there wasn't yet AW101 answer and I posted it.

AW101 Over a year ago

Exactly the same has happened to me before ;) Great minds think alike, +1

zb' · Accepted Answer · 2012-10-27 23:18:11Z

0

It can be done using temporary tables:

create temporary table arrayt (id int);
insert into arrayt values  ('2'),('2'),('3'),('3'),('3');
select catalog.* from arrayt a LEFT JOIN catalog on (a.id=catalog.id);

if you need count

select count(catalog.id) as count,catalog.id as id from arrayt a LEFT JOIN catalog on (a.id=catalog.id) group by catalog.id;

answered Oct 27, 2012 at 23:18

zb'

8,0793 gold badges44 silver badges68 bronze badges

Collectives™ on Stack Overflow

Count same values in mysql query

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related