MySQL Sum Group By

MySQL Group By and Sum total value of other column

Remove the single quote around the WORD. It causes the column name to be converted as string.

SELECT word, SUM(amount) 
FROM Data
Group By word

SUM() the results of GROUP BY column

Try this way

SELECT SUM(a.suma) from 
(SELECT (CASE
WHEN sum(cantidad) > 0 THEN 1
WHEN sum(cantidad) <= 0 THEN 0
END) AS suma FROM volcado GROUP BY reserva)a

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

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

Mysql Left Join And Sum not Working Correctly with group by month and year

You shouldn't use SUM(t2.total_amount) in the main query. You already calculated the sum in the subquery, you should use that. What's happening is that you're multiplying t2.total_amount by the number of rows in new_order that matches.

There's also no need to use IFNULL() inside SUM(), since SUM() ignores null values (most aggregation functions do).

The subquery should select the year and month of the date, so you can join on those directory, rather than using date_format.

And since you're grouping by month, you shouldn't select t1.order_date -- that will just pick a random day of the month from the group. You should just show the month in YYYY-MM format.

SELECT 
DATE_FORMAT(t1.order_date, '%Y-%m') AS month,
sum(t1.received_amt) as SumOfNO,
IFNULL(t2.Amount, 0) as SumOfSM,
sum(t1.received_amt) + IFNULL(t2.Amount, 0) AS Total
FROM `new_order` t1
LEFT JOIN
( select YEAR(t2.sell_date) AS year, MONTH(t2.sell_date) AS month, sum(total_amount) as Amount
from sell_master t2
group by year, month
) t2
ON YEAR(t1.order_date) = t2.year AND MONTH(t1.order_date) = month
GROUP BY month
ORDER BY month DESC

DEMO

Select the sum of values from 2 tables and group by common foreign key

Is this what you want?

select u.*,
(select sum(a.score) from a where a.id_user = u.id_user) as a_sum,
(select sum(b.score) from b where b.id_user = u.id_user) as b_sum
from user u;

If you want the total score, just add them together:

select u.*,
( (select COUNT(1) from a where a.id_user = u.id_user) +
(select coalesce(sum(b.score), 0) from b where b.id_user = u.id_user)
) as ab_sum
from user u;

MYSQL SUM(IF) with group by NAME but some NAME wanna makes sum

I understand that you want to group together customers A and B. You can do this with a CASE expression:

SELECT 
(CASE WHEN customer IN ('A', 'B') THEN 'A' ELSE Customer
END) as real_customer,
SUM(CASE WHEN outputDate >= '2020-03-01' AND outputDate < '2020-04-01' THEN SupplyPrice ELSE 0 END) AS March
FROM `TableOutput`
GROUP BY real_customer
ORDER BY March DESC
LIMIT 20;

Note that I modified the date predicate on outputDate so it uses comparisons to date litterals rather than using date functions; this is a more efficient approach, that may take advantage of a possibly existing index.

MYSQL SUM GROUP BY

GROUP BY has to come before ORDER BY:

  SELECT SUM(g.points) 
FROM GRADES g
WHERE g.date < 'thedate'
GROUP BY g.assignmentid
ORDER BY g.date DESC

Divide the sum of each group by the grand total

You can combine aggregate and window functions together:

select name
, sum(qt) as sum_qt
, sum(qt) / sum(sum(qt)) over () * 100 as pct_qt
from t
group by name

MYSQL sum/Count fails inside inner join group by

You can join the tables on either of the 2 conditions and use conditional aggregation:

SELECT e.employee_id, 
e.employee_Name,
SUM(s.employee_id = e.employee_id) AS saleCount,
SUM(CASE WHEN s.employee_id = e.employee_id THEN s.grand_total ELSE 0 END) AS totalSalesRevenue,
SUM(s.employee2 = e.employee_Name) AS helperEmpCount
FROM employee e LEFT JOIN sale s
ON s.employee_id = e.employee_id OR s.employee2 = e.employee_Name
GROUP BY e.employee_id;


Related Topics



Leave a reply



Submit