CODEWITHSUNDEEP
sqloracle-databasegroup-bycount
Mr Josh
Mr Josh

Reputation: 140

how to get count of a column along with column from another table

is there any other way for this problem .I wanted to use group by cant use ii. my solution:

SQL> select (select count(dr_id) 
     from cus_detail c 
     where c.dr_id=d.dr_id) as count_cus,dr_name from driver_detail d;

 COUNT_CUS DR_NAME
---------- ---------------
     1 raju
     0 mandi
     2 sajuman
     3 babu ram coi
     0 daju
     0 bare babu

Upvotes: 2

Views: 165

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think you want a basic aggregation query. Your method is correct (although it could be formatted better). The more standard method is join/group by:

select d.dr_name, count(c.dr_id) 
from driver_detail d left join
     cus_detail c 
     on c.dr_id = d.dr_id 
group by d.dr_name;

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

I'm not totally sure I understand what you're asking but I think you're asking how to group things and bring in data from another table

I typically prefer to do this as a grouping subquery:

select 
  d.dr_name,
  c.ctr
from 
 (select dr_id, count(dr_id) ctr from cus_detail group by dr_id) c
INNER JOIN driver_detail d ON c.dr_id=d.dr_id

The reason why here is that by grouping on the id when counting means we can keep a count for two different people (i.e. different id) both called John. If instead we joined on id then grouped on the Name then two different people both called John would have their counts added together

It's also good to group and count in a subquery because sometimes grouping and counting in the outer query introduces wrong counts if three or more tables are related in 1:Many fashion. If a record in a had 3 records in b and 4 records in c, and we join all before we count we will count 12 records. If we group first we will be joining in 1:1 relationship instead and the counter won't inflate erroneously

Upvotes: 1

Popeye
Popeye

Reputation: 35900

If two DR_NAME are same then GROUP BY can give different result then what your current query will give.

So better to use an analytical function as the following:

SELECT
    COUNT(C.DR_ID) OVER(
        PARTITION BY C.DR_ID
    ) AS COUNT_CUS,
    DR_NAME
FROM
    DRIVER_DETAIL D
    JOIN CUS_DETAIL C ON ( C.DR_ID = D.DR_ID )

Cheers!!

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

use this one.

select count(c.dr_id) as count_cus, d.dr_name 
    from driver_detail d
    inner join cus_detail c on c.dr_id=d.dr_id
    group by d.dr_name

Upvotes: 1

Related Questions