How to query the SUM of a column twice for two different states with given date range in SQL?-CodePudding

I am using MySQL Database. I have a table called 'membership'. Each Row contain information of members joined on a particular date, district and state. I want to retrieve the sum of members_joined in two different columns for two different states with given date range. Let the resultset skip those row if both state doesn't contain data on that date.

Table Definition is :

CREATE TABLE membership
(
int int not null AUTO_INCREMENT,
state VARCHAR(20),
district VARCHAR(20),
date date
members_joined int, 
PRIMARY KEY (id),
);

The Expected ResultSet :

DATE       | FIRST_STATE| FIRST_STATE_COUNT | SEC_STATE | SEC_STATE_COUNT   |
------------------------------------------------------------------------------
2021-01-10 | KA         |   1000            |   MH      | 1200               |
2021-01-09 | KA         |   500             |   MH      | 550                |
2021-01-08 | KA         |   0               |   MH      | 100                |
2021-01-07 | KA         |   50              |   MH      | 0                  |

I tried with Self Joining. But getting empty result set.

select A.state as FIRST_STATE , sum(A.members_joined) as FIRST_STATE_COUNT, B.state as SEC_STATE , sum(B.members_joined) as SEC_STATE_COUNT
FROM membership A, membership B
WHERE A.state<>B.state and A.state='KA' and B.state='MH'
and A.date BETWEEN '2021-01-10' and '2020-10-07'
and B.date BETWEEN '2021-01-10' and '2020-10-07'

CodePudding user response：

SELECT date,
       'KA' AS first_state, 
       SUM(CASE WHEN state = 'KA' THEN members_joined ELSE 0 END) AS first_state_count,
       'MH' AS sec_state, 
       SUM(CASE WHEN state = 'MH' THEN members_joined ELSE 0 END) AS sec_state_count
FROM membership
WHERE date BETWEEN '2021-01-10' and '2020-10-07'
GROUP BY date