CODEWITHSUNDEEP
sqlms-access
Tariq
Tariq

Reputation: 49

Count distinct values of a Column based on Distinct values of First Column

I am dealing with a huge volume of traffic data. I want to identify the vehicles which have changed their lanes, I'm Microsoft Access with VB.Net.

Traffic Data:

Vehicle_ID   Lane_ID   Frame_ID   Distance  
        1        2        12        100  
        1        2        13        103  
        1        2        14        105  
        2        1        16        130  
        2        1        17        135  
        2        2        18        136  
        3        1        19        140  
        3        2        20        141

I have tried to distinct the Vehicle_ID and then count(distinct Lane_ID). I could list the distinct Vehicle_ID but the it counts the total Lane_ID instead of Distinct Lane_ID.

SELECT
    Distinct Vehicle_ID, count(Lane_ID)
FROM Table1
GROUP BY Vehicle_ID

Shown Result:

Vehicle_ID  Lane Count  
       1    3  
       2    3  
       3    2

Correct Result:

Vehicle_ID  Lane Count  
       1    1  
       2    2  
       3    2

Further to that i would like to get all Vehicle_ID who have changed their lane (all data including previous lane and new lane). Output result would be somehow like: Vehicle_ID Lane_ID Frame_ID Distance
2 1 17 135
2 2 18 136
3 1 19 140
3 2 20 141

Upvotes: 0

Views: 171

Answers (3)

forpas
forpas

Reputation: 164099

Access does not support COUNT(DISTINCT columnname) so do this:

SELECT t.Vehicle_ID, COUNT(t.Lane_ID) AS [Lane Count]
FROM (
  SELECT DISTINCT Vehicle_ID, Lane_ID FROM Table1
) AS t
GROUP BY t.Vehicle_ID

So

to identify the vehicles which have changed their lanes

you need to add to the above query:

HAVING COUNT(t.Lane_ID) > 1

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you want vehicles that have changed their lanes, then you can do:

SELECT Vehicle_ID,
       IIF(MIN(Lane_ID) = MAX(Lane_ID), 0, 1) as change_lane_flag
FROM Table1
GROUP BY Vehicle_ID;

I think this is as good as counting the number of distinct lanes, because you are not counting actual "lane changes". So this would return "2" even though the vehicle changes lanes multiple times:

    2        1        16        130  
    2        1        17        135  
    2        2        18        136  
    2        1        16        140  
    2        1        17        145  
    2        2        18        146

Upvotes: 0

Jacky Wijaya
Jacky Wijaya

Reputation: 107

SELECT
  Table1.Vehicle_ID,
  LANE_COUNT
FROM Table1
JOIN (
   SELECT Vehicle_ID, COUNT(*) as LANE_COUNT FROM (
      SELECT distinct Vehicle_ID, Lane_ID FROM Table1
   ) dTable1 # distinct vehicle and land id
   GROUP BY Vehicle_ID # counting the distinct
) cTable1 ON cTable1.Vehicle_ID = Table1.Vehicle_ID # join the table with the counting

I think you should do one by one,

  1. Distinct the vehicle id and land id
  2. counting the distinct combination
  3. and merge the result with the actual table.

Upvotes: 0

Related Questions