[MySQL] SUM van afgeleidde per row

[nihitsia]

Ik ben via Cakephp aan een systeem bezig. Ik wil een virtueel veld dat het saldo van iemand berekent. Het saldo is de som van alle transacties.

Database ziet er als volgt uit:

CREATE TABLE IF NOT EXISTS `transactions` (
`id` bigint(20) NOT NULL auto_increment,
`user_id` int(11) NOT NULL,
`amount` int(11) NOT NULL,
`datetime` datetime NOT NULL,
PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`avatar` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
)

Een user heeft meerdere transacties. De foreign key is user_id.

De SQL query die ik nu heb is:

SQL:

SELECT `User`.`id`, `User`.`name`, `User`.`avatar`, (SELECT SUM(`transactions`.`amount`) FROM transactions, users WHERE `transactions`.`user_id` = `users`.`id`) AS `User__balance` FROM `users` AS `User` WHERE 1 = 1 LIMIT 20

Maar dit werkt niet. Wat doe ik verkeerd?

[KabouterSuper]

wat je de foutmelding? Of krijg je het verkeerde resultaat?

[nihitsia]

Ik krijg voor elke user dezelfde balance (namelijk het totaal van alle transactions). Er moet nog ergens in de subquery gespecificeerd worden dat alleen de sum wordt gepakt van de user met het user_id waar ze in de bovenste query ook mee bezig zijn. Maar ik zou niet weten hoe.

[RobIII]

Zou je niet eens gaan kijken naar joins en een group by?
Hoe werken joins?
Hoe werkt dat GROUP BY nu eigenlijk?

[nihitsia]

Lastige is dat CakePHP me een beetje dwingt om met subqueries te werken. Ik kan alleen binnen de haakjes werken.

[KabouterSuper]

Je queriet ook het totaalbalans in je subquery, dus erg verwonderlijk is het niet dat je voor elke user het totaal krijgt. Een lesje joins en aggregate functies zoals RobIII suggereert kan geen kwaad.

Je zou moeten denken aan zoiets (hoewel ik je afraad om op deze manier subqueries te gebruiken):

code:

SELECT `User`.`id`, `User`.`name`, `User`.`avatar`, user__balance.tot_amount FROM users as user, 
(SELECT SUM(`transactions`.`amount`) tot_amount FROM transactions, users WHERE `transactions`.`user_id` = `users`.`id` group by 'user.id') AS `User__balance`
 WHERE User.id = User__balance.id

[ Voor 13% gewijzigd door KabouterSuper op 03-03-2010 13:52 ]

[NMe]

Waarom heb je een users- en een User-tabel? En waarom die join in de subquery?

Als het zonder joins moet, dan kan dat vast ook wel zo:

SQL:

SELECT `User`.`id`, `User`.`name`, `User`.`avatar`, (SELECT SUM(`transactions`.`amount`) FROM transactions WHERE `transactions`.`user_id` = `User`.`id`) AS `User__balance` FROM `users` AS `User` WHERE 1 = 1 LIMIT 20

KabouterSuper: dat gaat niet werken, want nu krijg je op de plek waar één veldje moet staan meerdere waarden terug. Het enige dat de topicstarter nodig heeft is een correlatie tussen hoofdquery en subquery.

[ Voor 21% gewijzigd door NMe op 03-03-2010 13:53 ]

[Creepy]

En even een tikje door naar PRG.

[KabouterSuper]

@NME: ik had mijn post al getypt voordat TS met de opmerking kwam dat het niet als join mocht. Als je goed kijkt, dan zie je dat ik het sum-stuk als sub-tabel heb omgeschreven. Dat mag wel, maar werkt wel met een join.

[NMe]

KabouterSuper schreef op woensdag 03 maart 2010 @ 14:06:
@NME: ik had mijn post al getypt voordat TS met de opmerking kwam dat het niet als join mocht. Als je goed kijkt, dan zie je dat ik het sum-stuk als sub-tabel heb omgeschreven. Dat mag wel, maar werkt wel met een join.

Dat stond er pas na je edit, en als een join niet kan omdat hij alleen de select-list aan kan passen, dan kan jouw oplossing net zo min.

[KabouterSuper]

haha, je hebt helemaal gelijk...ik had de foute query gepost, en gelijk daarna ge-edit. En inderdaad....ik gebruik een join.