Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
machine
Starting Member
1 Post |
Posted - 2014-08-21 : 16:03:53
|
SELECT `loanac`.`id`, `loanac`.`name`, `loanac`.`lacc`, SUM(`loantrans`.`in`) as totalin, SUM(`loantrans`.`out`) as totaloutFROM loanac, loantransWHERE `loanac`.`lacc`=`loantrans`.`account`GROUP BY `loanac`.`lacc`Here is my query above which is working fine to show all accounts, BUT I need to find accounts where SUM(out) > SUM (in) instead of all accountsI've tried to add condition using AND but its showing error, can anyone help?Here is the db link in case if u want to take a look https://www.dropbox.com/sh/o0ro7r23ztnpfky/AABrfLYUB5DUk-FRvPegHZF_aMachine |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-21 : 16:13:56
|
[code]; WITH CTE (id, name, lacc, totalin, totalout)AS( SELECT loanac.id, loanac.name, loanac.lacc, SUM(loantrans.in) as totalin, SUM(loantrans.out) as totalout FROM loanac JOIN loantrans ON loanac.lacc=loantrans.account GROUP BY loanac.lacc)SELECT *FROM CTEWHERE totalout > totalin[/code]Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-08-21 : 16:55:15
|
Alternative:SELECT `loanac`.`id`, `loanac`.`name`, `loanac`.`lacc`, SUM(`loantrans`.`in`) as totalin, SUM(`loantrans`.`out`) as totaloutFROM loanacINNER JOIN loantrans ON `loanac`.`lacc`=`loantrans`.`account`GROUP BY `loanac`.`lacc`HAVING SUM(`loantrans`.`out`)>SUM(`loantrans`.`out`) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-08-21 : 17:14:27
|
Correction to my last post. Out should be greater than In, and not like I wrote Out greater than Out, which is rubbish.quote: Originally posted by bitsmed Alternative:SELECT `loanac`.`id`, `loanac`.`name`, `loanac`.`lacc`, SUM(`loantrans`.`in`) as totalin, SUM(`loantrans`.`out`) as totaloutFROM loanacINNER JOIN loantrans ON `loanac`.`lacc`=`loantrans`.`account`GROUP BY `loanac`.`lacc`HAVING SUM(`loantrans`.`out`)>SUM(`loantrans`.`outin`)
|
|
|
|
|
|
|
|