Combine Mysql Queries Columnwise

Is there anyway to combine mysql queries columnwise? I have below queries select count(ticket_id) as numInProgress from ost_ticket where status = 'in progress' and topic_id not i

Solution 1:

Just use these as subqueries in a select statement, e.g.

SELECT ($statement1$),($statement2$)

or with your queries:

SELECT (select count(ticket_id) as numInProgress from ost_ticket where status = 'in progress' and topic_id not in (select topic_id from ost_org_site_map)) as numInProgress ,(select count(ticket_id) as numAssigned from ost_ticket where status = 'open' and topic_id not in (select topic_id from ost_org_site_map)) as numAssigned;

Solution 2:

You may try this:

SELECT (select count(ticket_id) as numInProgress 
        from ost_ticket where status = 'in progress' 
        and topic_id not in (select topic_id from ost_org_site_map)),
       (select count(ticket_id) as numAssigned 
        from ost_ticket where status = 'open' 
        and topic_id not in (select topic_id from ost_org_site_map));

ie combine both the queries to make a subquery in the select statement.

Solution 3:

Perhaps this:

SELECT sum(CASE OT.status when 'in progress' ,1,0) as numInProgress,
       sum(CASE OT.status when 'open' ,1,0) as numAssigned 
FROM ost_ticket OT
LEFT JOIN OST_ORG_SITE_MAP OSM
 ON OT.topic_Id =OSM.Topic_ID
WHERE OT.status in ('in progress', 'open')
  and OSM.Topic_ID is null

The left Join allows us to join OST_TICKET and OST_ORG_SITE_MAP on topic_Id. When there is no topic ID in the site_map table then we have an equivalent to topic_Id not in...

we combined the status to only look for in progress and open and we use a case statement on status to determine what needs to be counted. Since count will return values for 1 and 0, we needed to use sum to add up the 1's and 0's instead.