Best way to count a filtered table

user_14498834 0
Viewed 40

I have a large table with this structure:

myTable:                       otherTable:
+----------+------+-------+    +-----+-------+-----+
|   Date   | Type | Ref   |    | ID  | Field | Ref |
+----------+------+-------+    +-----+-------+-----+
| 1/1/2020 |    1 | XXX   |    |  1  | Val1  | XXX |
| 1/1/2020 |    2 | XXX   |    |  2  | Val2  | YYY |
| 1/2/2020 |    1 | YYY   |    |  3  | Val3  | AAA |
| ...      |  ... | ...   |    | ... | val4  | BBB |
+----------+------+-------+    +-----+-------+-----+

I'd like to count all the rows with a certain Type, in a specific Date interval with a single Ref value.

Result:
+-----+-------+-----+-------+
| ID  | Field | Ref | Count |
+-----+-------+-----+-------+
|  1  | Val1  | XXX |     3 |
|  2  | Val2  | YYY |     1 |
|  3  | Val3  | AAA |     0 |
| ... | val4  | BBB |     2 |
+-----+-------+-----+-------+

Is this the best way to count values after the date filter?

SELECT otherTable.* ,
   ( SELECT Count(*) 
      FROM  myTable
      WHERE  myTable.Type IN ( 2, 12 ) 
        AND myTable.Ref = otherTable.Ref 
        AND Date BETWEEN Dateadd('d', now(), -7) AND Dateadd('d', now(), +7)
    ) as myCount
FROM otherTable

Or maybe this?

SELECT otherTable.* ,
   ( SELECT Count(*) 
      FROM  ( SELECT * FROM myTable
        WHERE  myTable.Type IN ( 2, 12 ) 
          AND myTable.Ref = otherTable.Ref 
          AND Date BETWEEN Dateadd('d', now(), -7) AND Dateadd('d', now(), +7)
      )
    ) as myCount
FROM otherTable

I'm learning SQL and looking for way to save performances. Thank you in advance!

ms-access sql
1 Answers

A JOIN on Ref fields may be more efficient than having this match in WHERE clause. Maybe should do a GROUP BY query for the count on myTable then join that query to otherTable:

SELECT otherTable.*, Cnt 
FROM otherTable 
INNER JOIN
   (SELECT Ref, Count(*) AS Cnt 
    FROM myTable
    WHERE [Type] IN (2, 12) 
    AND [Date] BETWEEN Dateadd('d', Date(), -7) AND Dateadd('d', Date(), +7)
    GROUP BY Ref) AS myCount
ON otherTable.Ref = myCount.Ref;
user_76071900
Related