Multiple sum in SQL query

Question

SELECT sum( plot_status = 'OPEN' ) AS OPEN 
     , sum( plot_status = 'SOLD' ) AS SOLD
FROM `tbl_plot`
GROUP BY `plot_status

This is giving

OPEN   SOLD
7       0
0       8

How to make it

OPEN  SOLD
7      8

Or is it possible?

John Woo · Accepted Answer · 2013-04-30 09:56:03Z

2

just remove the GROUP BY clause and it will work.

SELECT sum( plot_status = 'OPEN' ) AS `OPEN` ,
       sum( plot_status = 'SOLD' ) AS SOLD
FROM  `tbl_plot`

answered Apr 30, 2013 at 9:56

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

user2334807 · Accepted Answer · 2013-04-30 09:58:30Z

1

If there is present plot_name or id then group by that not by plot_status:

SELECT sum( plot_status = 'OPEN' ) AS
OPEN , sum( plot_status = 'SOLD' ) AS SOLD
FROM `tbl_plot`
GROUP BY //`plot_name or plot_id

This will work for you for individual plot. And if you don't want that then remove the group by clause.

answered Apr 30, 2013 at 9:58

user2334807

Karan Gandhi · Accepted Answer · 2013-04-30 10:21:27Z

1

select * from ( select sum( plot_status = 'OPEN' FROM tbl_plot ) AS OPEN select sum( plot_status = 'SOLD' FROM tbl_plot ) As Sold )tbl

answered Apr 30, 2013 at 10:21

Karan Gandhi

1,4942 gold badges12 silver badges20 bronze badges