How to filter sql query and get average from the data-CodePudding

I have a 2 tables below. I am trying to get the risky shoppers. The risky shoppers are shoppers who have an average risk score of 100.00(rounded to 2 decimal places) or more

This is what I have so far

 select m.merchantName,t.hashedShopperReference,  AVG(t.riskScore) as averageRiskScore, COUNT(t.transactionId) as totalNumberofTransactions
from transaction as t
join merchant m on t.merchantId = m.merchantid 
where t.hashedShopperReference IN(select distinct(hashedShopperReference) 
 from transaction where t.merchantId = m.merchantid)
group by t.transactionId

which gives me answer

Candle Store Inc fe9ac6f772088b00c006ca6736f946a2 10.0000 1
Candle Store Inc e80999e5dbf13cfb9b832fd4618154ca 100.0000 1
Candle Store Inc e80999e5dbf13cfb9b832fd4618154ca 103.0000 1
Local Coffee Roastery LLC e80999e5dbf13cfb9b832fd4618154ca 105.0000 1
Local Coffee Roastery LLC ebc3f4508048e3e673b89d1f963f2e7a 101.0000 1

My query shows all the result even those below 100. How can I have my query only show the results above 100

CodePudding user response：

Try the query below

select m.merchantName,t.hashedShopperReference,  ROUND(AVG(t.riskScore),2) as averageRiskScore, COUNT(t.transactionId) as totalNumberofTransactions
from transaction as t
join merchant m on t.merchantId = m.merchantid 
where t.hashedShopperReference IN(select distinct(hashedShopperReference)
 from transaction where t.merchantId = m.merchantid)
group by t.transactionId
HAVING AVG(t.riskScore) >= 100.0
ORDER BY m.merchantName ASC, averageRiskScore DESC

CodePudding user response：

Append HAVING AVG(t.riskScore) >= 100.0 to the very end of the query you showed us, right after the GROUP BY but before any LIMIT or ORDER BY clause.

CodePudding user response：

id  name    grocery         price
1   milk    Green Shop      2.34
2   bread   Clark’s Grocery 3.56
3   bread   Super Market    4.15
4   milk    Super Market    1.80
5   bread   Grocery Amanda  2.26
6   milk    Violet Grocery  3.45
7   milk    Clark’s Grocery 2.10
8   bread   Violet Grocery  2.55
9   milk    Grocery Amanda  1.95

Let’s find the names of products in which the average price of each product among groceries is higher than 3.00.

SELECT name, AVG(price)
FROM product
GROUP BY name
HAVING AVG(price)>3.00;