CODEWITHSUNDEEP
phpmysql
user5563910
user5563910

Reputation:

How to select all distinct values except one in MySQL?

I have a MySQL query in PHP where I am getting all distinct values and then getting the sums of associated columns of these distinct values like this:

SELECT t.`fruits`,sum(coalesce(s.amount,0)),sum(coalesce(s.price,0))
FROM(SELECT DISTINCT `fruits` FROM `accouts`) t
LEFT OUTER JOIN `accounts` s
 ON(t.`fruits` = s.`fruits` AND s.`userid` = 1
AND s.`date` BETWEEN "2010-11-01" AND "2015-12-22")
GROUP BY t.`fruits`

How can I exclude a certain distinct value from this query? Specifically if the "fruits" column is empty? Because this returns data like this:

Oranges - 23 - 43
Pears   - 33 - 55
        - 12 - 13
Grapes  - 54 - 76

I want to exclude the distinct row where "fruits" is empty. How can I go about doing this?

Upvotes: 2

Views: 1660

Answers (1)

Dylan Su
Dylan Su

Reputation: 6065

Try this:

SELECT t.`fruits`,sum(coalesce(s.amount,0)),sum(coalesce(s.price,0))
FROM(SELECT DISTINCT `fruits` FROM `accouts`) t
LEFT OUTER JOIN `accounts` s
 ON(t.`fruits` = s.`fruits` AND s.`userid` = 1
AND s.`date` BETWEEN "2010-11-01" AND "2015-12-22")
WHERE t.`fruits` != ''
GROUP BY t.`fruits`

Upvotes: 1

Related Questions