Reputation: 65

Multiple sum in SQL query

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?

Upvotes: 0

Answers (3)

Reputation: 1494

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

Upvotes: 1

Reputation:

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.

Upvotes: 1

Reputation: 263683

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`

Upvotes: 2