CODEWITHSUNDEEP
sqlsqlite
user1782427
user1782427

Reputation: 790

Multiple SELECT on the same field in one statement

I got the following table:

**stats**

id INT FK
day INT    
value INT

I would like to create an SQL query that will sum the values in value column in the last day, last week and last month, in one statement.

So Far i got this:

select sum(value) from stats as A where A.day > now() - 1
union
select sum(value) from stats as B where B.day > now() - 7
union
select sum(value) from stats as C where C.day > now() - 30

This returns just the first sum(value), i was expecting 3 values to return.

Running: select sum(value) from stats as A where A.day > now() - X ( Where x = 1/7/30) in different queries works as it should.

What's wrong with the query? Thanks!

Upvotes: 4

Views: 167

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

UNION is implicit distinct. Use UNION ALL instead like so:

SELECT 'last day' ItemType, sum(value) FROM stats as A WHERE A.day > now() - 1
UNION ALL
SELECT 'last week', SUM(value) FROM stats as B WHERE B.day > now() - 7
UNION ALL
SELECT 'last month', SUM(value) FROM stats as C WHERE C.day > now() - 30

Note that: I added a new column ItemType to indicate what is the type of the sum value whether it is last day, last week or last month

Upvotes: 5

Related Questions