Add a where constraint to Select distinct

Question

Table Overview:

2 columns

i_trans_to and i_trans_amnt

i_trans_to consists of IDs and i_trans_amnt consists of amount of money the user has deposited in account

user may deposit only these amounts 300,175,75,40,20(so the i_trans_amnt column will contain only these values)

there may be multiple entries for IDS as the user can deposit number of times.

I want a query which will select

1.All users whose total deposited money is greater than 500

2.Of all these users the query must tell about how many types of entries he made(example: 5 entries of Rs.300 and 2 entries of Rs.175)

i was successfull in designing the query except for the constraint that the user must have deposited over rs.500 here's the query

    SELECT DISTINCT i_trans_to, 
                SUM( i_trans_amnt ),
                (SELECT COUNT(*) 
                 FROM transac 
                 WHERE i_trans_amnt=300 AND transac.i_trans_to=current.i_trans_to) AS level1,
                 (SELECT COUNT(*) 
                 FROM transac 
                 WHERE i_trans_amnt=175 AND transac.i_trans_to=current.i_trans_to) AS level2,
                 (SELECT COUNT(*) 
                 FROM transac 
                 WHERE i_trans_amnt=75 AND transac.i_trans_to=current.i_trans_to) AS level3,
                 (SELECT COUNT(*) 
                 FROM transac 
                 WHERE i_trans_amnt=40 AND transac.i_trans_to=current.i_trans_to) AS level4,
                 (SELECT COUNT(*) 
                 FROM transac 
                 WHERE i_trans_amnt=40 AND transac.i_trans_to=current.i_trans_to) AS level5,
                 (SELECT COUNT(*) 
                 FROM transac 
                 WHERE i_trans_amnt=20 AND transac.i_trans_to=current.i_trans_to) AS level6
FROM transac as current
WHERE SUM( current.i_trans_amnt )>500
GROUP BY i_trans_to

I tried adding at the end of query a where clause in which i wrote SUM(i_trans_amnt)> 500 but this gave me an error.

Any suggestions?

Maybe use a `HAVING ...` clause instead of `WHERE ...` ? – wildplasser Aug 10 '13 at 13:01 — wildplasser, Aug 10 '13 at 13:01

score 1 · Answer 1 · answered Aug 10 '13 at 13:28

I have changed your query, you can get the level totals using a set of case statements, this shall speed up your query.

SUM( i_trans_amount) can be checked in the HAVING clause, see Sylvain Leroux answer for details on this

SELECT 
    i_trans_to, 
    SUM( i_trans_amnt ),
    count(CASE WHEN i_trans_amnt=300 THEN 1 END ) AS level1,
    count(CASE WHEN i_trans_amnt=175 THEN 1 END ) AS level2,
    count(CASE WHEN i_trans_amnt=75 THEN 1 END ) AS level3           
FROM 
    transac as current
HAVING
    SUM( i_trans_amnt ) > 500
GROUP BY 
    i_trans_to

score 0 · Answer 2 · answered Aug 10 '13 at 13:07

I wrote this query by first making a query that lists all the IDs that have a sum of more than 500 amnt deposited. Then I used that query in a join to only keep those (all other IDs will not pass the JOIN condition). Then I simply group on both columns to be able to display for each ID, for each amount, the number of deposits.

SELECT
  transac.i_trans_to,
  transac.i_trans_amnt,
  COUNT(0) AS number_of_times_amount_was_deposited
FROM
  transac
  JOIN (
    SELECT
      i_trans_to
    FROM
      transac
    GROUP BY
      i_trans_to
    HAVING
      SUM(i_trans_amnt) > 500
  ) AS large_depositors ON transac.i_trans_to = large_depositors.i_trans_to
GROUP BY
  i_trans_to,
  i_trans_amnt

score 0 · Answer 3 · edited May 23 '17 at 12:28

0

See WHERE vs HAVING _{(please credit Quassnoi on that post for this answer)}:

WHERE is applied before GROUP BY, HAVING is applied after (and can filter on aggregates).

Aggregates functions are SUM(), COUNT() and all those functions that works on groups of rows: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

edited May 23 '17 at 12:28

Community

1
1

answered Aug 10 '13 at 13:28

Sylvain Leroux

50,096
7
103
125

Add a where constraint to Select distinct

3 Answers3