SQL query assigment

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:

SELECT u.name SUM(s.amount) AS amount, COUNT(s.amount) AS count
FROM dbuser AS u JOIN dbsale AS s
ON u.id = s.idagent
ORDER BY s.amount ASC
HAVING s.amount >= 20 AND MONTH(s.date)='3';

SELECT DISTINCT u.name
FROM dbuser AS u INNER JOIN dbsale AS s
ON u.id = s.idagent
WHERE s.amount < 20 AND MONTH(s.date)='3' ;

Answer

Your query 1 will be-

SELECT 
A.`name`, 
SUM(B.amount) AS amount, 
COUNT(B.amount) AS count 
FROM DBUser A
INNER JOIN DBSale B ON A.ID = B.IDAgent
WHERE MONTH(CAST(B.date AS DATE))=3
AND B.amount >= 20
GROUP BY A.`name`

And query 2 will be-

SELECT A.`name`
FROM DBUser A
INNER JOIN DBSale B 
ON A.ID = B.IDAgent
WHERE MONTH(CAST(B.date AS DATE))=3
GROUP BY A.`name`,A.ID
HAVING SUM(CASE WHEN B.Amount > 20 THEN 1 ELSE 0 END) > 0

Add order by as per your requirement.

Advertisement

Answer