Hi,
This has also been updated, there is an issue with the way the conditions in the having are evaluated.
HAVING count(cust_id) > 2 AND city OR phone IS NOT NULL
should be
HAVING COUNT(cust_id) > 2 AND (city IS NOT NULL OR phone IS NOT NULL)
You need to have the criteria attached to both of the boolean checks (for city and for phone). It could be that sqlite lets you do this, by evaluating the 'city' as true automatically, then it would be 'TRUE OR phone IS NOT NULL' which would automatically mean its always going to be TRUE.
Thanks,
Adam.
ANONYMOUS wrote:
> Hello,
>
>
> Worksheet number 2 has a question that goes as follows:
>
> Q) Display credit code, and the count of publishers in a column called Number of
> publishers. Then group according to the creditcode for all the cases where either the
> count is greater than 2 or the city or phone is not empty. Then apply ascending order.
>
> Solution:
>
> SELECT creditcode, count(cust_id) AS 'Number of Publishers'
> FROM publishers
> GROUP BY creditcode
> HAVING count(cust_id) > 2 and city OR phone IS NOT NULL
> ORDER BY creditcode ASC;
>
>
> My query is that:
>
> 1) In the HAVING clause, shouldn't the answer by "OR city OR phone IS NOT NULL"? Why is it and when the question explicitly states "either or"
>
> 2) Can we have an alternative solution that goes like this, using the where clause:
>
> select creditcode, count(cust_id) as "number of publishers"
> from publishers
> where city or phone is NOT NULL
> group by creditcode
> having count(cust_id)>2
> order by count(cust_id) desc;
>
> Would this be incorrect? If so then why?
>
> Thanks
