CODEWITHSUNDEEP
mysqljoin
batfastad
batfastad

Reputation: 2013

MySQL query to list records with a count later related records

I have a table called invoices with 3 fields (for the purposes of this Q): 

invoice_id, company_id, invoice_stamp

invoice_id is the PK, index on company_id and invoice_stamp is a unix timestamp in integer form.

I'm trying to find the best way to get a list of invoices but alongside each invoice, a count of how many invoices in the table have a matching company_id and have an invoice_stamp greater than that of the current record.

So it needs a join on itself but I'm not totally sure how to construct this, whether it's a derived table in the FROM clause, or an INNER JOIN or LEFT JOIN.

So my output would hopefully look something like this: 

invoice_id | company_id | future invoice count
-----------------------------------------------
 1001       | 12345      | 5
 1002       | 67890      | 5
 1003       | 67890      | 4
 1004       | 67890      | 3
 1005       | 12345      | 4
 1006       | 12345      | 3
 1007       | 12345      | 2
 1008       | 67890      | 2
 1009       | 67890      | 1
 1010       | 12345      | 1
 1011       | 12345      | 0
 1012       | 67890      | 0

Is that even possible with MySQL or should I process something like this on the MySQL client side?

Anyone got any ideas?

Upvotes: 0

Views: 73

Answers (1)

Cfreak
Cfreak

Reputation: 19319

You could do it with a sub-select (untested but should work)

SELECT invoice_id, company_id,
   (SELECT count(invoice_id) 
       FROM invoice i2 WHERE i2.invoice_stamp > i.invoice_stamp)
   AS future_invoice_count
FROM invoice i;

Upvotes: 1

Related Questions