Grouping By Two Column But Only Showing One

Question

I'm running the following script.

select PlateID1, PlateID2, Max(TimeStamp) as LastVisit, Min(TimeStamp) as FirstVisit,
       count(PlateID1) as VisitCount
from ActivityLog
where (TimeStamp > '9/15/2017 12:00:00 AM' and TimeStamp < '9/18/2017 12:00:00 AM')
group by PlateID1, PlateID2
having  count(PlateID1) > 1
order BY VisitCount desc

Returns the following results:

PlateID1  PlateID2  LastVisit                 FirstVisit               VisitCount
7BAY665   _blank_   2017-09-15 19:28:20.457   2017-09-15 13:24:25.770  3621
_blank_   _blank_   2017-09-17 15:48:48.753   2017-09-15 12:55:46.557  305
7BAY665   7BAY665   2017-09-15 19:26:26.040   2017-09-15 19:05:21.627  5
7BAY665   _blank_   2017-09-15 19:17:03.170  2017-09-15 19:16:41.943   2

I need to omit the blanks, and combine the results for PlateID1 and PlateID2. So, every time a plate repeats in PlateID1 OR PlateID2, I need to group/count it.

The results I want, again, sorry for the poor formatting should be like this.

   PlateID  LastVisit                 FirstVisit               VisitCount
   7BAY665 2017-09-15 19:28:20.457    2017-09-15 13:24:25.770  3628

Based on Tab's comment, I came up with the following solution. Thanks!

SELECT t1.plateid, 
       Max(t1.timestamp) AS LastVisit, 
       Min(timestamp)    AS FirstVisit, 
       Count(plateid)    AS VisitCount 
FROM   ((SELECT plateid1 AS PlateID, 
                timestamp 
         FROM   activitylog 
         WHERE  ( timestamp > '9/15/2017 12:00:00 AM' 
                  AND timestamp < '9/18/2017 12:00:00 AM' ) 
                AND plateid1 IS NOT NULL 
                AND plateid1 <> '') 
        UNION 
        (SELECT plateid2 AS PlateID, 
                timestamp 
         FROM   activitylog 
         WHERE  ( timestamp > '9/15/2017 12:00:00 AM' 
                  AND timestamp < '9/18/2017 12:00:00 AM' ) 
                AND plateid2 IS NOT NULL 
                AND plateid2 <> '')) AS t1 
GROUP  BY t1.plateid 
HAVING Count(plateid) > 1

Show us the expected result as well! (Formatted text here too.) — jarlh, Sep 18 '17 at 13:09
\_blank\_ means NULL or the string literal '\_blank\_'? Tip: `COALESCE`/`IFNULL` and `IIF`/`CASE..WHEN`. — Pred, Sep 18 '17 at 13:12
Yea, i couldn't show the blank. It just means null/empty string. — Matthew, Sep 18 '17 at 13:15
It won't let me edit now. The expected results would be one row, with one PlateID column and the count increased to 3628. — Matthew, Sep 18 '17 at 13:19
The end result is supposed to count the number of times a vehicle has visited a site.. Whether the plate value repeats in the plateid1 column or the plateId2 column. — Matthew, Sep 18 '17 at 13:25

Tab Alleman · Answer 1 · 2017-09-18T13:55:27.003

0

In a CTE or derived table, UNION ALL two queries. In the first select PlateID1 as PlateID, and in the second, select PlateID2 as PlateID. In both queries, include all the other columns you are currently showing. Use a WHERE clause to eliminate NULL/empty PlateIDs.

Then use your current query to SELECT from the CTE, and GROUP BY PlateID.

edited Sep 18 '17 at 13:55

answered Sep 18 '17 at 13:27

Tab Alleman

31,483
7
36
52

score 0 · Answer 2 · answered Sep 18 '17 at 13:29

You can try using coalesce

select COALESCE(PlateID1, PlateID2) PlateID, 
       Max(TimeStamp) as LastVisit, 
       Min(TimeStamp) as FirstVisit,
       count(PlateID1) as VisitCount
from ActivityLog
where (TimeStamp > '9/15/2017 12:00:00 AM' and TimeStamp < '9/18/2017 12:00:00 AM')
AND PlateID1 IS NOT NULL AND PlateID2 IS NOT NULL
group by COALESCE(PlateID1, PlateID2)
having  count(PlateID1) > 1
order BY VisitCount desc

Grouping By Two Column But Only Showing One

2 Answers2