SUM(ROUND function Gives Error "invalid use og group function"
SELECT sale_time as sale_date,
CONCAT(ospos_people.first_name, ' ' ,ospos_people.last_name ) AS
CustomerName, ospos_sales_items.sale_id,
comment,payment_type,
customer_id,
employee_id,
ospos_items.item_id,
supplier_id,
quantity_purchased,
item_cost_price,
item_unit_price,
percent as item_tax_percent,
discount_percent,
SUM(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)
as subtotal,
ospos_sales_items.line as line,
serialnumber,ospos_sales_items.description as description,
ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*
(1+(SUM(percent)/100)),2) as total, SUM(
ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(percent)/100),2))
as tax,
SUM((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)
- (item_cost_price*quantity_purchased)) as profit
FROM ospos_sales_items
INNER JOIN ospos_sales ON ospos_sales_items.sale_id=ospos_sales.sale_id
INNER JOIN ospos_items ON ospos_sales_items.item_id=ospos_items.item_id
LEFT OUTER JOIN ospos_suppliers ON
ospos_items.supplier_id=ospos_suppliers.person_id
LEFT OUTER JOIN ospos_sales_items_taxes ON
ospos_sales_items.sale_id=ospos_sales_items_taxes.sale_id and
ospos_sales_items.item_id=ospos_sales_items_taxes.item_id and
ospos_sales_items.line=ospos_sales_items_taxes.line
INNER JOIN ospos_people ON customer_id = ospos_people.person_id
GROUP BY customer_id
No comments:
Post a Comment