Sto jednostavniji query

[ m_k @ 02.11.2006. 08:25 ] @

Imam tebelu koja izgleda ovako:

Code:
id_usera | mod | score
---------+-----+-------
1       | ctf | 12
1       | sd  | 32
2       | tdm | 42
1       | ctf | 34
1       | tdm | 64
2       | sd  | 54
1       | tdm | 2

E sad treba mi da selektujem SUM(score) za sve gdje id_usera=1 i mod=ctf, pa SUM(score) gdje id_usera=1 i mod=sd..... pa SUM(score) gdje id_usera=1 (bez moda).

Na kraju bi rezultat trebao izgledati ovako:

Code:
________________
id_usera=1
ctf=46
sd=32
tdm=66
total=144

________________
id_usera=2
ctf=0
sd=54
tdm=42
totla=96

Eh kako sastaviti query da se sto brze izvrsi?

[ shketuljko @ 03.11.2006. 19:12 ] @

Code:
SELECT SUM( score )
FROM `tabela`
WHERE id_usera = 1
GROUP BY mod
LIMIT 0 , 30

Ovo ti je za jednog usera!

[ m_k @ 04.11.2006. 00:15 ] @

Pomoglo mi je tnx ali sad imam novi problem.

Imam query:

SELECT SUM(data) AS data, SUM(data1) AS data1, id_usera FROM tabela GROUP BY id_usera ORDER BY data+data1 DESC

probao sam i sa:

SELECT SUM(data) AS data, SUM(data1) AS data1, id_usera, data+data1 AS total FROM tabela GROUP BY id_usera ORDER BY total DESC

ali ne radi.

Kako da ovo izvedem (znaci da soritram po zbiru data+data1)?

[ shketuljko @ 05.11.2006. 14:45 ] @

Ako mozes objasni malo ljepse sta treba da se uradi pa da razmotrimo!!!

[ zikaa @ 05.11.2006. 14:59 ] @

SELECT SUM(`data`) AS `data`, SUM(data1) AS data1, SUM(`data`+data1) AS total , id_usera FROM tabela GROUP BY id_usera ORDER BY total DESC

[ shketuljko @ 07.11.2006. 12:14 ] @

Primjer:

Code:
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `user` int(11) NOT NULL default '0',
  `mod` varchar(255) NOT NULL default '',
  `score` int(11) NOT NULL default '0',
  `score1` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

INSERT INTO `test` VALUES (1, 1, 'ctf', 12, 2);
INSERT INTO `test` VALUES (2, 1, 'sd', 32, 3);
INSERT INTO `test` VALUES (3, 2, 'tdm', 42, 5);
INSERT INTO `test` VALUES (4, 1, 'ctf', 34, 2);
INSERT INTO `test` VALUES (5, 1, 'tdm', 64, 23);
INSERT INTO `test` VALUES (6, 2, 'sd', 54, 2);
INSERT INTO `test` VALUES (7, 1, 'tdm', 2, 3);

Rjesenje:

Code:

SELECT SUM( `score` ) AS `data` , SUM( `score1` ) AS data1, SUM( score + score1 ) AS total, user
FROM `test`
GROUP BY user
ORDER BY total DESC
LIMIT 0 , 30