CODEWITHSUNDEEP
mysqlsqlgroup-by
And Row ID
And Row ID

Reputation: 197

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      nb_billings
  1             3
  2             2

What I want :

sub_id      nb_billings
  1             3
  1             3
  1             3
  2             2
  2             2

Thanks

Upvotes: 1

Views: 74

Answers (4)

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

I guess this should suffice :

SELECT      s.subscriber_id,
            s.billing_id,
            s.TotalCount
FROM        (
                SELECT      subscriber_id,
                            billing_id,
                            COUNT(billing_id) AS TotalCount

                FROM        BILLING
                GROUP BY    subscriber_id,
                            billing_id
            ) s
GROUP BY    s.subscriber_id,
            s.TotalCount,
            s.billing_id

ORDER BY    s.subscriber_id

This should give you the result as follows :

subscriber_id   billing_id  TotalCount
1                   10a         2
1                   10b         2
1                   10c         1
2                   10a         1
2                   10b         1
2                   10c         3
2                   10d         1

You can see this here -> http://rextester.com/AVVS23801

Hope this helps!!

Upvotes: 1

Rich Benner
Rich Benner

Reputation: 8113

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.

Upvotes: 1

Bhavesh Ghodasara
Bhavesh Ghodasara

Reputation: 2071

select subscriber_id,count(billing_id)over(partition by subscriber_id)
from billing

will do just that.

Upvotes: -1

Blank
Blank

Reputation: 12378

You can use a subquery to do that:

SELECT 
    (SELECT count(t2.billing_id) FROM billing t2 WHERE t2.subscriber_id = t1.subscriber_id),
    t1.subscriber_id
FROM billing t1

Upvotes: 1

Related Questions