3xi 3xi -4 years ago 73
SQL Question

Get information about an invoice and return the number of invoice items on it with a single SELECT comand

I'm using the chinook database and sqlite3. My goal is to return a list of invoices with the invoice id, invoice date and the number of items on the invoice for a specific customer. The first two are pretty simple,

SELECT InvoiceId, InvoiceDate
FROM invoices
WHERE CustomerId = 2;


returns:

1 |2009-01-01 00:00:00
12 |2009-02-11 00:00:00
67 |2009-10-12 00:00:00
196|2011-05-19 00:00:00
219|2011-08-21 00:00:00
241|2011-11-23 00:00:00
293|2012-07-13 00:00:00


However, the invoice line items are in another table. I can count the ones that correspond to specific invoices with:

SELECT count(*)
FROM invoice_items
WHERE Invoiceid = 12;


which returns
14


But I want to return a list like:

1 |2009-01-01 00:00:00|2
12|2009-02-11 00:00:00|14

Answer Source

You need an inner join on your query; it will look something like this:

SELECT 
    invoices.InvoiceId, invoices.InvoiceDate,
    COUNT(DISTINCT(items.ID)) AS Items 
FROM 
    invoices
JOIN 
    invoice_items AS items ON invoices.ID = itemsID
GROUP BY invoices.InvoiceId

Of course I'm guessing the names of your ID columns

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download