[HELP] MySQL tables join with sum

netomx

Hi, I'm looking to show some tables with a percentage of a sum. Example, original table:

"Order1", "Product1", "3"
"Order1", "product2", "5"
Add a new column as:
"Order1", "Product1", "3", "3/8"
"Order1", "product2", "5", "5/8"

The 3/8 and 5/8 are as a percentage.

Thanks

drazilox

Do you mean like this?

SELECT column1, column2, column3, (column3 / 8) * 100 FROM table

ricardo

CF keeps blocking my post.

If you're just adding /8 as a string onto the end of it then look at CON*CAT

CON*CAT( field3 , '/8' ) AS field4

ehab

this is one of netomx interview question he didn't manage to finish... yet

Falzo

@drazilox said:

probably not, as the 8 will be more like the (changeable) sum of the third column.

so more likely

SELECT *, col3/(SELECT SUM(col3) as sum3 FROM table)*100 as col4 FROM table

probably you want to group the inner select by the first column, if this should be the order

netomx

@Falzo is right, it is a change. And I found it by myself:

SELECT table1.order_no, table1.product, quantity, (quantity/(select sum(quantity) from table2 where order_no = table1.order_no)) AS percent FROM table_original AS tabla1 LEFT JOIN product ON table1.product = product.product ORDER BY order_no, product ASC

Falzo

@netomx that's how your question made enough sense ;-)
and your problem probably was a simple SUM results in returning a single row instead of a table...

SUM by default groups all rows of the whole table if there is no GROUP BY in the clause. so you need to separate this via an extra select like you found it. yet you may replace your where clause with GROUP BY table1.order_no ;-)

netomx

You seem to be a MySQL expert, I need more help