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

Question

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

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 — Tariq, May 10 '19 at 10:13

forpas · Answer 1 · 2019-04-25T11:13:44.660

3

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

edited Apr 25 '19 at 11:13

answered Apr 25 '19 at 10:28

forpas

160,666
10
38
76

1

I see you frequent the `ms-access` tags and at times point out the dialect's limitation such as your first sentence which you also mentioned a [month ago](https://stackoverflow.com/a/55329044/1422451). I agree, so much so, I recently added a [suggestion](https://access.uservoice.com/forums/319956-access-desktop-application/suggestions/37193662-upgrade-access-sql-dialect-to-current-ansi-standar) to the Access team the high time to upgrade its SQL dialect to current standards. Please vote, comment, share! – Parfait Apr 25 '19 at 13:44
I did not even remember that post! Your suggestion makes sense, upvoted. – forpas Apr 25 '19 at 15:15
Thanks Forpas...It is absolutely perfect for me. – Tariq Apr 25 '19 at 15:17
If it worked, you may consider accepting the answer. – forpas Apr 25 '19 at 15:18

Jacky Wijaya · Accepted Answer · 2019-04-25T10:19:28.133

0

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,

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

edited Apr 25 '19 at 10:19

answered Apr 25 '19 at 09:02

Jacky Wijaya

107
1
2

Thanks Jacky. But it shows " Syntax error in FROM clause" – Tariq Apr 25 '19 at 15:11

score 0 · Answer 3 · answered Apr 25 '19 at 10:56

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

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

3 Answers3