Openbravo Forum End of Life Notice

Dear Openbravo Forum User,

Because of continued very low usage we decided to stop the forums on 31st of August 2017

In case of questions: webmaster "at" openbravo.com

Product Sales Analysis

<<

Ronny _G

Posts: 910

Joined: Wed Mar 18, 2009 7:26 pm

Post Sat May 22, 2010 1:25 am

RE:Product Sales Analysis

All good. .  Thanks for sharing.


Download also available here now (Sales Analysis [iruragachara]).



Kind Regards,


Ronny G


----------------------------------------------------------


OBPOS 2.30 (upgraded), Windows & MySQL 5.0


Openbravo POS Reports, Openbravo POS au

<<

Gjergj Sheldija

Posts: 28

Joined: Wed Mar 18, 2009 7:25 pm

Post Mon Aug 16, 2010 8:44 am

RE:Product Sales Analysis

i think there is an error in the query,

because it always gives a gain/loss that is equal to the

product price.

i fixed it with the following query ..


hope it's ok


 


regards


gjergj


 




 

Code: Select all
SELECT
PRODUCTS.CODE,
TICKETS.TICKETID,
RECEIPTS.DATENEW AS DATE,
PRODUCTS.NAME,
PRODUCTS.PRICEBUY,
PRODUCTS.PRICESELL,
SUM(TICKETLINES.UNITS) AS UNITSSOLD,
SUM(TICKETLINES.UNITS * TICKETLINES.PRICE) AS TOTAL,
SUM(TICKETLINES.UNITS*(PRODUCTS.PRICESELL-PRODUCTS.PRICEBUY)) AS PROFITLOSS
FROM RECEIPTS, TICKETS, TICKETLINES, PRODUCTS
WHERE RECEIPTS.ID = TICKETS.ID AND TICKETS.ID = TICKETLINES.TICKET AND TICKETLINES.PRODUCT = PRODUCTS.ID
GROUP BY PRODUCTS.CODE, PRODUCTS.NAME , TICKETS.TICKETID
ORDER BY RECEIPTS.DATENEW
<<

Randall Vazquez

Posts: 8

Joined: Mon May 24, 2010 9:12 am

Post Wed Oct 06, 2010 5:50 am

RE:Product Sales Analysis

Hi,


I have been trying to get this report to work but I have been getting an error regarding the usage of RECEIPTS.DATENEW in the SQL query.  This is true both when ran from the POS and from SquirrelSQL.


I am currently using Derby.


The  error reads : Column reference 'RECEIPTS.DATENEW' is invalid, or is part of an invalid expression.  For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions.


Any ideas?


Randall.

Previous

Return to Submit a Report

Who is online

Users browsing this forum: No registered users and 1 guest

cron
Website Terms


Designed by ST Software for PTF.