CODEWITHSUNDEEP
mysql
S.A
S.A

Reputation: 15

Revising my query

A question for homework is to show the total amount of houses with multiple presents. The list below shows which ones they are but I cannot work out the query to show them as a total of 6. I am still new and learning Mysql, my apologies for the ignorance.

Mysql data

**address**                   **Number of presents per home**
2 Bay Road                              2
2a Neptune Road                         2
45 Bay Road                             2
59 Black Street                         2
65 Mainway Avenue                       3
89 White Street                         2

Query used:

SELECT address, SUM(goodBehaviour) AS `Number of Houses with Multiple presents`
FROM wishlist
GROUP BY address
HAVING SUM(goodBehaviour) >1;

I have tried a few other queries to total the Address column but have not been able to show my desired output. Thanks.

Upvotes: 1

Views: 42

Answers (2)

Marcin C.
Marcin C.

Reputation: 169

If you need total number of houses - you can use your query as subquery:

SELECT count(*) FROM (SELECT address, SUM(goodBehaviour) AS `Number of Houses with Multiple presents`
FROM wishlist
GROUP BY address
HAVING SUM(goodBehaviour) >1) x;

Upvotes: 0

Shadow
Shadow

Reputation: 34231

The problem is that you sum the goodBehaviour field's values, but you should count the number of addresses that have more than 1 presents.

If each address has just 1 record in your table (based on your sample data):

select count(address)
from wishlist
where goodBehaviour >1

If you can have multiple records for a single address, then in a subquery you need to sum the number of presents and count the number of addresses in the outer query, where the total number of presents are more than 1:

select count(address)
from
    (select address, sum(goodBehaviour) as presents
     from wishlist
     group by address) t
where t.presents>1

Upvotes: 1

Related Questions