A different kind of counting in SQL

Applications Programming
1

I am looking to do a count on a database table where some of the elements count double. Say the data is as follows:

id value
1 X
2 Y
3 X
4 X
5 Y

A regular count (SELECT value, COUNT(*) FROM data GROUP BY value) would yield:

X 3
Y 2

However, Y happens to count double so the answer should be:

X 3
Y 4

How do I achieve this in SQL? I am using MySQL 5.2.

2

Hi figaro.

Not sure if this is the best answer you'll get, but not sure how you'd do that in a simple group by statement.

SELECT value, COUNT(*) FROM data where value != 'Y' group by value
union
SELECT value, COUNT(*)*2 FROM data where value = 'Y' group by value

V   COUNT(*)
- ----------
X          3
Y          4
3

Thanks, that is actually very straightforward.