CODEWITHSUNDEEP
mysqlsqlgroup-bysum
Afif Zuhair
Afif Zuhair

Reputation: 93

how to SUM a column by different condition then group by date

I tried to sum values of a column (subtotal) into different columns(total, rejected, actual) that differentiate by its condition of a column (state).

Actual Table (ode_orders)

Delivery Date Subtotal State
1/2/2021 150.00 Delivery Completed
2/2/2021 45.00 Rejected by business
2/2/2021 45.00 Delivery Completed
2/2/2021 87.00 Rejected by business
3/2/2021 45.00 Delivery Completed
3/2/2021 70.00 Delivery Completed

I want it to make it as new table as:

Delivery Date Total Sales Rejected Actual
1/2/2021 150 0 150
2/2/2021 177 132 45
3/2/2021 155 0 155

I tried using this query:

SELECT  delivery_date, 
  SUM(subtotal) AS TotalSale,
  SUM(subtotal) WHERE state NOT LIKE %Completed% AS TotalSale
FROM ode_orders
GROUP BY delivery_date;

The results

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE state NOT LIKE %Completed% AS TotalSale
FROM ode_orders
GROUP BY deliver' at line 3

Upvotes: 1

Views: 2108

Answers (2)

shohel
shohel

Reputation: 44

select * 
,(totalSales-Rejected) Actual
from
  (
  select deliveryDate 
  ,sum(subTotal) totalSales 
  ,isnull((
  select sum(subTotal)  from ode_orders sa where sa.deliveryDate=a.deliveryDate and 
  [state]='Rejected by business'
  ),0) Rejected
  from ode_orders a
  group by deliveryDate
) mt

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use conditional aggregation

 SELECT delivery_date, SUM(subtotal) AS TotalSale,
 SUM(case when State='Rejected by business' then subtotal else 0 end) as Rejected ,
SUM(case when State='Delivery Completed' then subtotal else 0 end) as actual 
 from table_name group by delivery_date

Upvotes: 4

Related Questions