MySQL SUM while keeping Summed Data Seperate

3990 views php
3

Wondering if someone can point me in the right direction.

I am trying to build some reports, querying a sales table, I have a issue where using SUM and Group by, but still wanting to use the original lines.

For example:

Select StockCode, SUM('Sell' * Qty) as Total from 'Sales'
Group by StockCode

What i would like is to display the results as a SUM, But i want to also retain the Qty.

So the output would be something like:

StockCode Qty Total.

I can obviously run some PHP to do the calculation easy enough, but i was trying to complete as much as possible in SQL Queries to avoid unnecessary cluttered code.

Is there a simple way to do this, or would you advise just doing the calculations in PHP.

Table Example:

StockCode   Qty  Sell
1234       2      1.99
5468       1      0.99
2456       2      2.99
1234       3      1.99
5648       1      2.99
2546       2      4.99
2456       3      2.99

Sell is Per Item

Example:

StockCode   Qty  Sell    Total
1234       2      1.99   3.98
5468       1      0.99   0.99
2456       2      2.99   5.98
1234       3      1.99   5.97
5648       1      2.99   2.99
2546       2      4.99   9.98
2456       3      2.99   8.97

Results example:

1234       5      1.99   9.95
5468       2      0.99   1.98
2456       5      2.99   14.95

answered question

Do you have access to MySQL server version 8.0.2 and above ? (window functions ?)

Couldn't Sell possibly be different for each grouped row? What value do you want to show in your result in that case? Qty could be represented by SUM(Qty), since the SUM sums up the grouped entries beneath it.

4 Answers

4

Have you tried Select StockCode, Qty, Sell, SUM('Sell' * Qty) as Total from 'Sales' Group by StockCode, Total ?

posted this
9

it may be problem in "quote", you can try this

Select StockCode, SUM(`Sell` * `Qty`) as Total from `Sales`
Group by StockCode

posted this
1

If I understand correctly you need to do this:

SELECT StockCode, Sell, SUM(Qty) AS Quantity_Sold_At_This_Price, SUM(Sell * Qty) AS Total_For_Quantity_Price
FROM t
GROUP BY StockCode, Sell

posted this
8

You can try below

select StockCode, Qty,Sell,Qty*Sell as total from
(select StockCode, sum(Qty) as Qty, min(Sell) as Sell
from 'Sales'
group by StockCode
)X

posted this

Have an answer?

JD

Please login first before posting an answer.