Complex MySql Query

[paulman888888]

Hello Hello,

I'm trying to get a rank from a voteing table. My table holds the vote in a coloum which is 1 for a like and 2 for a dislike.

 

What i want to do is get rank of an id. I know what i need to do but i dont no how.

I need to;

Count the amount of 1's in the column vote

Count the amount of 2's in the colomn vote

Take the amount of 2's away from the amount of 1's then order it by the amount left.

 

Can some please help me?

Paul

P.S Am sorry for the (really) bad words but i couldn't get it out of my head 

[Mchl]

Hello Hello,

I'm trying to get a rank from a voteing table. My table holds the vote in a coloum which is 1 for a like and 2 for a dislike.

 

Why not 0 and 1? Would make such thing a bit easier...

 

Anyway

SELECT id, SUM(IF(voteColumn =1,1,0)) AS like, SUM(IF(voteColumn = 2,1,0)) AS dontLike FROM table GROUP BY id ORDER BY like - dontLike DESC

 

[paulman888888]

Thank-you Mchl,

I've edited it and its still not working :: Hmm...

 

	$result=mysql_query("
SELECT table_id,
table_name,
SUM(IF(vote = 1,1,0)) AS like,
SUM(IF(vote = 2,1,0)) AS dontLike
FROM votes
WHERE table_name = \"lyrics\"
GROUP BY table_id
ORDER BY (like - dontLike) DESC")or die(mysql_error());
////// And below is the creation code for the table
CREATE TABLE IF NOT EXISTS `votes` (
  `id` int(100) NOT NULL auto_increment,
  `table_name` varchar(100) NOT NULL,
  `user_id` int(100) NOT NULL,
  `table_id` int(100) NOT NULL,
  `vote` int(1) NOT NULL default '0',
  `date_voted` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1453 ;

[Mchl]

'Not working' as in?

Do you get any errors?

 

BTW: There is no difference between INT(100) and INT(1).

[paulman888888]

I always thought the number in the brackets for INT(n) is the length. With INT(1) i can have 1-9 and with INT(2) i can have from 1-99. I might be wrong and if i am i would like to be corrected. I also think that INT(-1) is infinity.

 

Anyways... The error;

I get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'like, SUM(IF(vote = 2,1,0)) AS dontLike FROM votes WHERE table_name = "lyrics' at line 3

 

I just don't no what's up with it. I can normally sort this error when it happens.

 

Please help

Thank-you all again

Paul

[Mchl]

Oh... I know what's up. You cant use 'like' as a column alias, because it's MySQL's reserved word. Unless you backtick it.

 

This should work:

SELECT id, SUM(IF(voteColumn =1,1,0)) AS sumLike, SUM(IF(voteColumn = 2,1,0)) AS sumDontLike FROM table GROUP BY id ORDER BY sumLike - sumDontLike DESC

 

 

The number in parentheses has nothing to do with what numbers you can store in a field. It just tells MySQL how to display these numbers.

 

See here:

http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

 

[paulman888888]

You learn something new everyday.

 

I try yours and i get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table GROUP BY id ORDER BY sumLike - sumDontLike DESC' at line 1

.

 

When i try my moddifyed version of yours i get,

// the code used
"
SELECT table_id,
table_name,
SUM(IF(vote=1,1,0)) AS dodlike,
SUM(IF(vote=2,1,0)) AS dontLike
FROM votes
WHERE table_name = \"lyrics\"
GROUP BY table_id
ORDER BY (dodlike - dontLike) DESC"

//

//The error;

Reference 'dodlike' not supported (reference to group function)

[Mchl]

The first one is obviously because you should put your actualtable name in place of 'table' but you figured this out.

The second one I almost forgot about. You have to work around it like this

 

SELECT * FROM (
  SELECT table_id,
    table_name,
    SUM(IF(vote=1,1,0)) AS dodlike,
    SUM(IF(vote=2,1,0)) AS dontLike,
    SUM(IF(vote=1,1,0)) - SUM(IF(vote=2,1,0)) AS score
  FROM votes
  WHERE table_name = 'lyrics'
  GROUP BY table_id
) AS sq
ORDER BY score DESC