Hi, I want to Count the number of occurences of data in a table, then multiply it in a value from other table, After that I want to sum up the product of count and the value from other table, As of now, I cant count and then multiple but I cant Sum them Up.
This is my SQL code:
SELECT orderline.product_id, COUNT(orderline.product_id) AS counted, products.orig_price, (COUNT(orderline.product_id)*products.orig_price) AS product FROM orderline INNER JOIN products ON products.product_id = orderline.product_id GROUP BY orderline.product_id
This will return:
product_id | counted | orig_price | product| 1 | 4 | 2 | 8 | 2 | 3 | 5 | 15 |
Then I want to SUM the product to get the total amount. How can I do that?
I tried adding this
SUM (COUNT(orderline.product_id)*products.orig_price) AS Sum
But it gives mes error: Function db.SUM does not exist. Check the Function Name Parsing and Resolution section in the manual. :(
Any Idea on how can I achieve what I want? Thanks in advance.