And Row ID And Row ID - 10 days ago 5
MySQL Question

Having the number of line having a specifc ID without group by SQL

I have a 'billing' table which represent all instances of billings from my subscribers. A subscriber can have multiple billings.

I have a simple SQL request which is :

SELECT count(billing_id),subscriber_id
FROM billing
group by subscriber_id


As a result I have a list of all my subscribers with the number of billings they've made.
I want to have a list of all the billings no grouped by subscribers, but I want the result of the previous request appearing in each lines.

Example:
Result of my previous request:


  • sub_id: 1, nb_billings: 3

  • sub_id: 2, nb_billings: 2



What I want :


  • sub_id: 1, nb_billings: 3

  • sub_id: 1, nb_billings: 3

  • sub_id: 1, nb_billings: 3

  • sub_id: 2, nb_billings: 2

  • sub_id: 2, nb_billings: 2



Thanks

Answer

I'd do it like this;

SELECT
    b.subscriber_id
    ,a.billing_count
FROM billing b
JOIN (SELECT subscriber_id, count(billing_id) billing_count FROM billing GROUP BY subscriber_id) a
    ON b.subscriber_id = a.subscriber_id

The subquery works out the count of billing_id by subscriber, this is then joined to all rows of your original table (using subscriber_id). This should give the result you're after.