MySQL Group by Sum

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

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 Calculate sum IF by Group

I think you want:

SELECT product_name,
    SUM(quantity*price) as `Value (qty x price)`,
    CASE WHEN SUM(quantity) > 100 THEN 0.04 ELSE 0.02 END 
        * SUM(quantity * price) as `Distributor Commission`
FROM transaction_details
GROUP BY product_name;

Rationale:

• you want to compute the commission over on the total sales value (so you need a SUM() on the right side of the multiplication)

• identifiers (such as column aliases for example) should be surrounded with backticks rather than single quotes

• I rewrote the IF condition as a CASE expression instead (although both are valid in MySQL, I like CASE better, because it is standard SQL)

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

MYSQL query group by and cumulative sum for financial orderbook

I use mysql 5.7 (since this version is using by me). you want to sum cummulative from the result of amount on each price, so you should use SUBQUERY in order to aggregation

Try this:

    set @CumulativeSum := 0;
    SELECT price, summ,
   (@CumulativeSum:= @CumulativeSum + summ) as cumsum 
      FROM (SELECT SUM(amount) as Summ
             FROM (SELECT * FROM orderbook) a
             GROUP BY price
             ORDER BY price) b

result

+-------+------+--------+
| price | summ | cumsum |
+-------+------+--------+
|    10 | 1.00 | 1.00   |
|    20 | 3.50 | 4.50   |
|    21 | 3.00 | 7.50   |
+-------+------+--------+

this is the fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4f607e38a23f069829dfaa177a8bc3d3

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

Can we group sum in previous period in SQL

First of all, i would change the name of your columns (year, month and order are mysql reserved words or functions). You could do it with a subquery like this:

select customer, 
       `year`, 
       `month`, 
       `order`, 
        (select sum(output)
         from yourtable b 
         where a.customer=b.customer and (a.year>b.year or (a.year=b.year and a.month>b.month)))
from yourtable a

What you do is you get every row and the last column is the sum of output for all other columns with the same customer and have year lower than the year in your customer or if the year is the same, the month is lower

---

Related Topics