I've been struggling with a query for a while now, and would be grateful if anyone here could help. The database is for an invoicing system, with the following tables & fields involved in said query:
Invoice (id, orderDate)
Product (id, productName)
Invoice_Item (id, invoice_id, product_id, quantity)
Basically, I've been trying to write some SQL which returns a list of all products in the product table along with the total quantities of these products sold (in invoices) between two dates.
So far - my query returns a list of products where total quantity sold is greater than zero, but does not return a complete list of products.
My query so far is below, any help would be greatly appreciated!
Expand|Select|Wrap|Line Numbers
- SELECT productName, SUM(quantity) AS quantity
- FROM Invoice
- INNER JOIN Invoice_Item ON Invoice.id=Invoice_Item.invoiceRecordID
- WHERE unix_timestamp(orderDate) >= $startDate AND unix_timestamp(orderDate) <= $endDate
- GROUP BY Product.id