SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

Nigger Goku: Dec 11, 2004; あっちに行け

I've got a table set up for allowing people to vote on content items, with the structure like so:

code:

INT id    INT content_id    ENUM(y,n) vote

The vote column is just a 'yes/no' on whether they liked that particular item or not.

The problem is trying to find the mostly liked content item from this table. The query I'm currently using is:

code:

SELECT
	`content_id`,COUNT(content_id) AS count
FROM
	`votes`
WHERE
	`vote` = 'yay'
GROUP BY
	`content_id`
ORDER BY
	`count` DESC
LIMIT 6

However, instead of just finding the items with the most 'yay' votes, I'd like to find the ones with the highest ratio of 'yay' votes to 'nay' votes.

Is there a particular way to assign ENUM values to 'fake' columns, so I could do '(yay/nay) AS ratio'? Or is there a better way to achieve the same result?

Any help is appreciated.

# ¿ Dec 3, 2007 01:00

Adbot: ADBOT LOVES YOU

# ¿ Apr 29, 2024 03:07

Nigger Goku: Dec 11, 2004; あっちに行け

Thanks for the help with the voting query, but I have another question regarding grouping DATETIME fields.

I've got a table called 'views' that's laid out like so:

code:

INT content_id, DATETIME added

If I wanted to generate a report showing the most popular time of day for views, I can do something like this:

code:

SELECT
	HOUR(added) AS hour,MINUTE(added) AS minute,COUNT(content_id) AS count
FROM
	`views`
GROUP BY
	`hour`,`minute`

My question is: is there anyway to group results in sets of 10 minutes, rather than single minutes in the query?

Ideally, I'd like to be able to plot the results out like how Steam does.

I've had a brief look at the INTERVAL function, but it's hard to find examples of how to use this in a GROUP query.

# ¿ Dec 10, 2007 01:32

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'