Member Avatar for ms061210

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.

  • 2 Contributors
  • 1 Reply
  • 282 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for diafol

Sounds like that you're using a reserved word (sum) in your sql without backticking it (`sum`).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.