1) Query that returns all agents who had sales greater or equal than 20 in month March, also sum of sales in month March greater or equal than 20 and number of sales in month March greater or equal than 20. Results sort in descending order. Colons: name, amount, count
2) Query that returns all agents who didn’t have even one sale greater than 20 in month March. Colon: name
Database:
DBSale
ID ____ IDAgent _____ Date ____ Amount
1 _______ 1 ______ 01-03-16 _____ 15
2 _______ 2 ______ 01-03-16 _____ 25
3 _______ 1 ______ 01-03-16 _____ 17
4 _______ 3 ______ 04-03-16 _____ 55
5 _______ 4 ______ 04-03-16 _____ 12
6 _______ 5 ______ 05-03-16 _____ 13
7 _______ 5 ______ 06-03-16 _____ 11
8 _______ 4 ______ 11-03-16 _____ 20
9 _______ 4 ______ 12-03-16 _____ 21
10 ______ 3 ______ 15-03-16 _____ 45
11 ______ 2 ______ 17-03-16 _____ 45
12 ______ 1 ______ 19-03-16 _____ 11
13 ______ 1 ______ 25-03-16 _____ 19
14 ______ 1 ______ 01-04-13 _____ 25
15 ______ 1 ______ 04-04-16 _____ 65
16 ______ 3 ______ 15-04-16 _____ 10
17 ______ 5 ______ 16-04-16 _____ 41
DBUser
ID ___ Name
1 ___ Mark Roseberg
2 ___ Don Smith
3 ___ Anna Twix
4 ___ Angela Reeves
5 ___ Juan Dovitoso
These are my incorrect tries:
1)
SELECT u.name SUM(s.amount) AS amount, COUNT(s.amount) AS countFROM dbuser AS u JOIN dbsale AS sON u.id = s.idagentORDER BY s.amount ASCHAVING s.amount >= 20 AND MONTH(s.date)='3';2)
SELECT DISTINCT u.nameFROM dbuser AS u INNER JOIN dbsale AS sON u.id = s.idagentWHERE s.amount < 20 AND MONTH(s.date)='3' ;Advertisement
Answer
Your query 1 will be-
SELECT A.`name`, SUM(B.amount) AS amount, COUNT(B.amount) AS count FROM DBUser AINNER JOIN DBSale B ON A.ID = B.IDAgentWHERE MONTH(CAST(B.date AS DATE))=3AND B.amount >= 20GROUP BY A.`name`And query 2 will be-
SELECT A.`name`FROM DBUser AINNER JOIN DBSale B ON A.ID = B.IDAgentWHERE MONTH(CAST(B.date AS DATE))=3GROUP BY A.`name`,A.IDHAVING SUM(CASE WHEN B.Amount > 20 THEN 1 ELSE 0 END) > 0Add order by as per your requirement.