A different kind of counting in SQL

figaro · #1 (**permalink**) 10-23-2009

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

Code:

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:

Code:

X 3
Y 2

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

Code:

X 3
Y 4

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

scottn · #2 (**permalink**) 10-23-2009

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.

Code:

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

figaro · #3 (**permalink**) 10-24-2009

Thanks, that is actually very straightforward.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
sed help,,kind of urgent!!	solaix14	Shell Programming and Scripting	3	03-10-2009 02:12 PM
A Different Kind of DoS Attack	iBot	IT Security RSS	0	09-17-2008 11:30 AM
Kind of knapsack problem	ngungo	Shell Programming and Scripting	3	11-08-2007 09:35 AM
What kind of Linux for the newbies?	sanlen	UNIX for Dummies Questions & Answers	2	10-01-2007 01:38 PM
what kind of UNIX	hiei	UNIX for Dummies Questions & Answers	2	04-12-2004 08:15 PM

Bits Awarded / Charged to scottn for this Post
Date	User	Comment	Amount
10-24-2009	figaro	N/A	1,000

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by