Wikibooks

Using POSper/Developer guide/SQL Queries

< Using POSper | Developer guide
This page may need to be reviewed for quality.

Postgres

Total Sales Per Date: 

SELECT date(tickets.datenew), 
SUM(productsout.price*productsout.units) AS total 
FROM tickets LEFT OUTER JOIN productsout ON tickets.ticketid=productsout.ticketid
GROUP BY date
ORDER BY date


Totals per product, per date, within a date range: 

SELECT date(datenew) AS theDate, name, SUM(units * price) AS price
FROM productsout
    LEFT OUTER JOIN products ON productsout.product=products.reference
    LEFT OUTER JOIN tickets on productsout.ticketid=tickets.ticketid

WHERE datenew BETWEEN 
'2006/2/16' AND 
'2006/2/19:23:59'

GROUP BY date(datenew),name
ORDER BY date(datenew),name


Reset Database without removing products 

?



MySQL:

Retrieved from "https://en.wikibooks.org/wiki/Using_POSper/Developer_guide/SQL_Queries"