need multiple outputs from single query - mysql

Question

I want following output in mysql using single query from single table.

Total  Pending   Critical   Completed
  120      45         45         30

right now I am doing using 4 queries for for different column like

select count(*) total from Y; -- for first column
select count(*) as Pending from Y where status = 0--- for second column
select count(*) as critical from Y where type = 'Critical' -- for third column

What I want, I want this in single query something like

select count(*) as total,count(*) pending where XYZ,

I am not getting what to write in where because conditions are different for each output, if we write

select count(*) as total,count(*) pending where status = 0

then it gives

total pending
  45      45

but I want

total pending
  120      45

Please help me,

Rahul Tripathi · Accepted Answer · 2015-11-13 07:05:19Z

1

You can try to use CASE WHEN THEN like this:

select SUM(CASE WHEN  status = 0 THEN 1 END) as 'Pending ',
       SUM(CASE WHEN   type = 'Critical' THEN 1 END) as 'critical',
       COUNT(*) as 'Total'
from Y

answered Nov 13, 2015 at 7:05

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

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

1 Comment

Ankit21ks Over a year ago

Thank you So Much...! It is working as required and I think it is the best way, Thank you so much again..

Rahul Tripathi · Accepted Answer · 2015-11-13 06:58:56Z

0

Best way I can see for you to do it is a query like

SELECT ( select count() total from Y) as total ,  
    ( select count() total from Y WHERE status = 0) as pending

edited Nov 13, 2015 at 6:58

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

answered Nov 13, 2015 at 6:58

Catprog

2674 silver badges14 bronze badges

1 Comment

Ankit21ks Over a year ago

ok but MySQL will execute these queries individually which is same as writing individual queries, Can we get in some more efficient way??

Collectives™ on Stack Overflow

need multiple outputs from single query - mysql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related