CODEWITHSUNDEEP
mysqlsqljoinsubquery
justWired
justWired

Reputation: 99

MySQL Sub select multiple condition

I'm trying to combine multiple select statements into 1 query but can't seem to get it right. I figure I would need to do a sub select. I'm sure it's something simple I'm overlooking. I have more after the 2nd query but they're all the same setup except different TAS_UID numbers.

Query 1:

SELECT USR_USERNAME AS 'User Name', 
           COUNT(AD.app_uid) AS 'Total'
FROM   APP_DELEGATION AD
JOIN   USERS U
WHERE  AD.USR_UID = U.USR_UID
AND    AD.DEL_THREAD_STATUS = 'Closed'
GROUP BY AD.USR_UID

Query 2:

SELECT USR_USERNAME AS 'User Name', 
       COUNT(AD.app_uid) AS 'Total for Task A'
FROM   APP_DELEGATION AD
JOIN   USERS U
WHERE  AD.USR_UID = U.USR_UID
AND    AD.DEL_THREAD_STATUS = 'Closed'
AND    AD.TAS_UID = '23423423455'
GROUP BY AD.USR_UID

Upvotes: 0

Views: 188

Answers (2)

nycdan
nycdan

Reputation: 2849

You could do that with a Case statement. Something like this:

SELECT USR_USERNAME AS 'User Name'
  ,COUNT(AD.app_uid) AS 'Total'
  ,SUM(case when AD.TAS_UID = '23423423455' then 1 else 0 end) as 'Total for Task A'
FROM APP_DELEGATION AS AD
  JOIN USERS AS U 
  ON AD.USR_UID = U.USR_UID
WHERE AD.DEL_THREAD_STATUS = 'Closed'
GROUP BY AD.USR_USERNAME

Upvotes: 1

Linger
Linger

Reputation: 15058

Your queries are not correct. They should probably be something like the following:

Query 1:

SELECT USR_USERNAME AS 'User Name', COUNT(AD.app_uid) AS 'Total'
FROM APP_DELEGATION AS AD
  JOIN USERS AS U 
  ON AD.USR_UID = U.USR_UID
WHERE AD.DEL_THREAD_STATUS = 'Closed'
GROUP BY AD.USR_UID

Query 2:

SELECT USR_USERNAME AS 'User Name', COUNT(AD.app_uid) AS 'Total for Task A'
FROM APP_DELEGATION AS AD
  JOIN USERS AS U 
  ON AD.USR_UID = U.USR_UID
WHERE AD.DEL_THREAD_STATUS = 'Closed'
  AND AD.TAS_UID = '23423423455'
GROUP BY AD.USR_UID

Upvotes: 2

Related Questions