CODEWITHSUNDEEP
mysqlsql
KostasC
KostasC

Reputation: 1106

How to combine 3 queries in 1

I have a accounting table, from this table I monitoring the activity of the users. I have the 3 following queries: First

select count(distinct username),
       DATE_FORMAT(StartTime,'%H %d/%m/%y')
from acct
where date(starttime) = date '2014-10-02'
group by DATE_FORMAT(StartTime,'%H %d/%m/%y')

Second

select count(distinct username),
       DATE_FORMAT(UpdateTime,'%H %d/%m/%y')
from acct
where date(updatetime) = date '2014-10-02'
group by DATE_FORMAT(UpdateTime,'%H %d/%m/%y')

Third

select count(distinct username),
       DATE_FORMAT(StopTime,'%H %d/%m/%y')
from acct
where date(stoptime) = date '2014-10-02'
group by DATE_FORMAT(StopTime,'%H %d/%m/%y')

I am little confused, it is possible to make them one query?I would like to check every hour of the day how many users is online. Can you guide me where to look to solve my problem? Thanks

Fields starttime, updatetime and stoptime is datetime.

Upvotes: 0

Views: 40

Answers (2)

Brewal
Brewal

Reputation: 8189

If you grab the same amount of fields in all the select queries, you can then use UNION. But in that case, you might just want to use OR : 

select COUNT(DISTINCT username) AS count_distinct_users,
       CASE WHEN date(StartTime) = date '2014-10-02' 
            THEN DATE_FORMAT(StartTime,'%H %d/%m/%y')
            WHEN date(UpdateTime) = date '2014-10-02' 
            THEN DATE_FORMAT(UpdateTime,'%H %d/%m/%y')
            WHEN date(StopTime) = date '2014-10-02' 
            THEN DATE_FORMAT(StopTime,'%H %d/%m/%y')
       END AS date
from acct
where date(StartTime) = date '2014-10-02' 
or date(UpdateTime) = date '2014-10-02'
or date(StopTime) = date '2014-10-02'
group by date

sqlFiddle demo

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

Try this using the UNION:

select count(distinct username),
       DATE_FORMAT(StartTime,'%H %d/%m/%y')
from acct
where date(starttime) = date '2014-10-02'
group by DATE_FORMAT(StartTime,'%H %d/%m/%y')
UNION
select count(distinct username),
       DATE_FORMAT(UpdateTime,'%H %d/%m/%y')
from acct
where date(updatetime) = date '2014-10-02'
group by DATE_FORMAT(UpdateTime,'%H %d/%m/%y')
UNION
select count(distinct username),
       DATE_FORMAT(StopTime,'%H %d/%m/%y')
from acct
where date(stoptime) = date '2014-10-02'
group by DATE_FORMAT(StopTime,'%H %d/%m/%y')

Upvotes: 0

Related Questions