Openbravo POS/Developer guide/SQL Queries

From Wikibooks, open books for an open world
< Openbravo POS‎ | Developer guide(Redirected from Using LibrePOS/Developer guide/SQL Queries)
Jump to: navigation, search

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