CODEWITHSUNDEEP
mysqlsql
Wiskoza
Wiskoza

Reputation: 13

How to connect two sql queries?

I am trying to connect two queries referring to the same table. I want to know how many fruits I got in the time range and how many of them were not fresh.

Query 1

SELECT COUNT(id) FROM fruits
WHERE date>='2017-04-01' AND date<='2017-04-30'

Query 2

SELECT COUNT(id) FROM fruits
WHERE fresh='no'

Upvotes: 1

Views: 146

Answers (2)

Martin Smith
Martin Smith

Reputation: 453426

You can use a CASE expression (NB: I am interpreting the "of them" in the question to be referring to fruits in the time range not fruits in general if you want fruits in general remove the where clause and move the predicate into another similar case expression)

SELECT COUNT(id), 
       SUM(CASE WHEN fresh = 'no' THEN 1 ELSE 0 END)
FROM fruits
WHERE date>='2017-04-01' AND date<='2017-04-30'

Upvotes: 2

yasgur99
yasgur99

Reputation: 858

I would use:

SELECT COUNT(id) FROM fruits
WHERE date >= '2017-04-01' AND date <= '2017-04-30' AND fresh = 'no';

In both queries 1 and 2, you want the same columns and just want to get the intersection of the two, so just add to the WHERE clause with an "AND".

Upvotes: 0

Related Questions