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.

3 Years
Discussion Span
Last Post by diafol
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.