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
Time Based Priority in Active Record Query
How to Pivot Data in Bigquery Standard SQL Without Manual Hardcoding
Introducing Foreign Key Constraint May Cause Cycles or Multiple Cascade Paths
Determine Contiguous Dates in SQL Gaps and Islands
Cascade Copy a Row with All Child Rows and Their Child Rows, etc
Sql: Parse Comma-Delimited String and Use as Join
How to Optimize This SQL Query (Using Indexes)
Split String Oracle into a Single Column and Insert into a Table
Ssms: How to Import (Copy/Paste) Data from Excel
Use Plink to Execute Command (Oracle SQL Query) on Remote Server Over Ssh
How to Create Unique Index Where Column Order Is Not Taken into Account (Set)
I Have a Delete-Insert Cte That Fails in a Strange Manner
Deleting Duplicate Records Using a Temporary Table
Multiple Column Values in a Single Row
Which Orm Frameworks Will Build and Execute the SQL Ddl for You