MySQL Sub select multiple condition

Question

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

what do you want your result to look like? Your 2nd query appears to be just a subset of your first. — nycdan
– nycdan, Commented Oct 28, 2013 at 17:38
@nycdan Result should display as follows: 1st column: user names, 2nd column: count of all taks, column 3: count of only specified task uid — justWired
– justWired, Commented Oct 28, 2013 at 17:55
okay, I added an answer that I think will help. My syntax might be slightly off, but it should get you there. — nycdan
– nycdan, Commented Oct 28, 2013 at 18:43

Linger · Accepted Answer · 2013-10-28 17:36:46Z

2

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

answered Oct 28, 2013 at 17:36

Linger

15.1k23 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Linger Over a year ago

Did my answer solve your problem or are you still in need of something further?

nycdan · Accepted Answer · 2013-10-29 14:07:49Z

1

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

edited Oct 29, 2013 at 14:07

answered Oct 28, 2013 at 18:00

nycdan

2,8592 gold badges23 silver badges34 bronze badges

3 Comments

justWired Over a year ago

wouldn't you need to remove the WHERE AND statement as it was already stated in the sum function? Also, what exactly is the case then 1 else 0 doing?

nycdan Over a year ago

You are correct. The AND should come out. I will edit accordingly. Thanks. The case statement adds up the number of rows that meet the criteria. It's basically a count.

justWired Over a year ago

ah that would make sense. Kinda got what else 0 was doing but was unsure of the 1. Thanks!

Collectives™ on Stack Overflow

MySQL Sub select multiple condition

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related