CODEWITHSUNDEEP
mysqlsql
Ask Warvin
Ask Warvin

Reputation: 63

Removing Zero values within two columns using MySQL

I have created a table - tblconsolidated which consist of the following columns and data.

| Code | Country   | City      |  Area | Amount    | Date       |
| 4001 | Singapore | Singapore |    A  | 5,000.00  | 2021-04-30 |
| 4001 | Russia    | Moscow    |    B  | 12,000.00 | 2021-04-30 |
| 4002 | Singapore | Singapore |    B  | 7,000.00  | 2021-04-30 |
| 5000 | Singapore | Singapore |    A  | 4,000.00  | 2021-04-30 |

I tried this mysql query to get the values of 4001 and 5000 and calculate the revenue per area and I have to exclude area that has no code equal with 4001 and 5000. Here's the code:

Select City,Area,FORMAT((CASE WHEN Code = '4001' THEN Amount ELSE 0 END),2) as sales,
FORMAT((CASE WHEN Code = '5000' THEN Amount ELSE 0 END),2) as expense from table1
WHERE Date ='2021-04-30' AND Country = 'Singapore' GROUP BY Area,City;

Output should be like this:

| City      | Area |  Revenue  | Sales    |   NET    |
| Singapore |  A   | 5,000.00  | 4,000.00 | 1,000.00 |

Any help would be truly appreciated. Thanks

Upvotes: 0

Views: 52

Answers (1)

Akina
Akina

Reputation: 42622

A pattern:

SELECT City, 
       Area, 
       SUM(t1.Amount) Revenue,
       SUM(t2.Amount) Sales,
       SUM(t1.Amount) - SUM(t2.Amount) NET
FROM table1 t1
JOIN table1 t2 USING (City, Area)
WHERE t1.code = 4001
  AND t2.code = 5000
GROUP BY City, 
         Area

Maybe the date must be added into joining expression or/and into WHERE/GROUP BY clause.

Add FORMAT() if needed.

Upvotes: 1

Related Questions