How to use "COUNT" with "GROUP BY" & "WHERE"

Question

SELECT `loanac`.`id`, `loanac`.`area`
FROM loanac, loantrans
WHERE `loanac`.`lacc`=`loantrans`.`account`
GROUP BY `loanac`.`lacc`
HAVING SUM(IFNULL(`loantrans`.`out`,0)) > SUM(IFNULL(`loantrans`.`in`,0))

QUERY RESULT:

----------------------------------------
id      area
----------------------------------------
5       A
165     C
166     C
167     C
232     D
91      B

This Query gives me all id & area for lacc's who have out>in, this query is is OK & working, I just need to get output GROUP BY area and COUNT id from this query. Can anyone help?

What I want is

----------------------------------------
COUNT(id)      GROUP BY(area)
----------------------------------------
1               A
5               B
9               C
1               D

NOTE: The conditions aren't change Target: Want to find/count number of (loanac.id) grouping by (loanac.area) with same conditions

`FROM loanac INNER JOIN loantrans ON loanac.lacc=loantrans.account` don't use old fashioned join syntax — Paul Maxwell, Sep 13 '14 at 22:19

Garr Godfrey · Answer 1 · 2014-09-13T22:06:07.983

0

i think you want something like this. Add area to your GROUP BY line, and use COUNT(id) to get the count of transactions.

SELECT count(`loanac`.`id`),
       SUM(IFNULL(`loantrans`.`out`,0)) AS transout,
       SUM(IFNULL(`loantrans`.`in`,0)) AS transin,
       `loanac`.`area`,
       `loanac`.`lacc`
FROM loanac, loantrans
WHERE `loanac`.`lacc`=`loantrans`.`account`
GROUP BY `loanac`.`lacc`,`loanac`.`area`
HAVING transout > transin

EDIT: To give output you are looking for


SELECT count(loanac.id), `loanac`.`area`
FROM loanac, loantrans
WHERE `loanac`.`lacc`=`loantrans`.`account`
GROUP BY `loanac`.`lacc`,`loanac`.`area`
HAVING SUM(IFNULL(`loantrans`.`out`,0)) > SUM(IFNULL(`loantrans`.`in`,0))

edited Sep 13 '14 at 22:06

answered Sep 13 '14 at 19:02

Garr Godfrey

8,257
2
25
23

I think I couldn't make it clear enough to understand, information is been updated. Will you please take a look again and suggest anything? – Machine Sep 13 '14 at 19:33
Bro, tried as you directed, but area is not grouping. Here is the db uploaded for you. Will you please take a look. https://www.dropbox.com/s/gz8f4ejvbgxehbp/bankdb.sql_2.zip?dl=0 – Machine Sep 14 '14 at 10:50

lurker · Accepted Answer · 2014-09-18T19:39:33.563

0

I think this does what you're asking for:

SELECT COUNT(id), area FROM
  (SELECT id, area FROM loanac
     JOIN loantrans ON loanac.lacc = loantrans.account
     GROUP BY loanac.lacc
     HAVING SUM(IFNULL(loantrans.out, 0)) > SUM(IFNULL(loantrans.in,0))) t
  GROUP BY area;

Above, I have a group by loanac.area because that's what the original example looks like. If you want to group by loanac.lacc, just change the GROUP BY above to, GROUP BY loanac.lacc.

EDIT: Loading in the test database linked in the comment and running the query yields:

+-----------+------+
| COUNT(id) | area |
+-----------+------+
|        86 | A    |
|        81 | B    |
|        64 | C    |
|        66 | D    |
|        20 | E    |
|         3 | F    |
+-----------+------+

edited Sep 18 '14 at 19:39

answered Sep 13 '14 at 20:01

lurker

56,987
9
69
103

Bro, tried as you directed BUT, this query is not maintaining this condition "HAVING SUM(loantrans.out) > SUM(loantrans.in)". Any suggestion? – Machine Sep 14 '14 at 10:55
@Machine I updated my answer and insert the `IFNULL`. I wasn't sure if it would work without it. See if that fixes it. – lurker Sep 14 '14 at 11:48
Bro, now it's not grouping by id, i need to group by id, then area. Here is my db uploaded for you, will u please take a look. https://www.dropbox.com/s/or8hf4rza2ut736/bankdb.sql_4.zip?dl=0 – Machine Sep 16 '14 at 10:13
@Machine what do you mean "group by id" in this case? The `id` doesn't appear in your example since you are just showing a count of multiple, different id's per area. Your example showed grouped and ordered by area. Perhaps you could show a more illustrative example. – lurker Sep 16 '14 at 10:23
Bro, please check the screenshot uploaded here https://www.dropbox.com/s/zcsuxn8gxd8gp37/Capture.PNG?dl=0 – Machine Sep 17 '14 at 13:27
@Machine your screen shot shows your tables plus the query you already have that you aren't happy with. You're saying to GROUP BY the `id`. But if you want a count of `id` as you show in your question under "What I want" then you cannot have both. If you group by `id`, you can't also provide a count of `id`. So it is unclear what you want your results to look like. Perhaps you could update the "What I want" table in your problem statement. It needs to show how you want to group by `id` and by `area`. Right now, it just groups by `area` and counts `id`. – lurker Sep 17 '14 at 19:53
Bro, screenshot updated https://www.dropbox.com/s/zcsuxn8gxd8gp37/Capture.PNG?dl=0 will you please take a look. Actually all I want is to find which area have how many active accounts. – Machine Sep 18 '14 at 11:24
@Machine I'm sorry, I don't see how the results of my answer differ from what's underlined as the requirement in that screenshot or your example desired result. See my updated answer for results from the data you linked. Let me know exactly what's not correct about it. – lurker Sep 18 '14 at 11:42
Bro, "count(loanac.id)" is counting id's multiple times, each id's can't be counted more than once, only then I'll get desired result. Any idea? :( – Machine Sep 18 '14 at 18:00
Bro, just little, little bit correction needed, the query is not maintaining this condition "HAVING SUM(IFNULL(loantrans.out,0)) > SUM(IFNULL(loantrans.in,0))". WIth or without this condition, result is same – Machine Sep 18 '14 at 18:58
@Machine I reconsidered the approach and leveraged your working `SELECT` in my latest update. – lurker Sep 18 '14 at 19:40
You did it! It worked! Thank you very much. May god fulfill your wishes :) – Machine Sep 19 '14 at 12:51
Bro can u help with this? http://stackoverflow.com/questions/26026668/how-to-use-opposite-of-between-in-a-mysql-query/26040410#26040410 Messed up with "NOT BETWEEN" here are the screenshots https://www.dropbox.com/sh/lgmop3zxqaf9mjy/AAAWVWjZXd8da9KDDOqz2ANla?dl=0 – Machine Sep 27 '14 at 09:01

How to use "COUNT" with "GROUP BY" & "WHERE"

2 Answers2