CODEWITHSUNDEEP
sqlsql-servert-sql
RazorKillBen
RazorKillBen

Reputation: 573

What is the standard practice way to return "totals" from other tables with SQL?

I have a simple table with users, a simple table with a row for each time a user completes work, and a third table with a row for each time a user is assessed. I want to build a view that shows me the total from each table. There are a myriad of ways to achieve this, but what is the most optimal, CPU-least intensive and standard practice way to do this?

I usually do this with OUTER APPLY but in this particular scenario, I feel a CTE would probably be more efficient or even a sub-query. I can't seem to get this right by using a JOIN and then COUNT/SUM and GROUP BY as it tends to duplicate or produce incorrect figures.

Here's how I'm currently doing this (minimal replicable example) but it feels unnecessarily slow:

SELECT u.[userID],
       u.[Name],
       u.[Team],
       w.[Work],
       q.[Quality],
       FORMAT(q.[Total] / w.[Total], 'P') as [%]
  FROM [Users] AS u
 OUTER APPLY
       (
           SELECT COUNT(*) AS [Total]
             FROM [Work] as w
            WHERE 1=1
                  AND w.[UserID] = u.[userID]
                  AND w.[Date] < DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 7) - 7)
       ) as [Work]
 OUTER APPLY
       (
           SELECT COUNT(*) AS [Total]
             FROM [Quality] as q
            WHERE 1=1
                  AND q.[UserID] = u.[userID]
                  AND q.[Date] < DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 7) - 7)
                  AND q.[Result] = 'FAIL'
       ) as [Quality]

Upvotes: 1

Views: 72

Answers (1)

Yair Maron
Yair Maron

Reputation: 1958

You may have better performance using CTE.

you can do it like this:

WITH WorkSums AS
(
    SELECT      w.[UserID],
                COUNT(*)    AS [Total]
    FROM        [Work]      AS w
    WHERE       w.[Date] < DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 7) - 7)
    GROUP BY    w.[UserID]
),

QualitySums AS
(
    SELECT      q.[UserID],
                COUNT(*)    AS [Total]
    FROM        [Quality]   AS q
    WHERE       q.[Date] < DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 7) - 7)
                q.[Result] = 'FAIL'
    GROUP BY    q.[UserID]
)

SELECT  u.[userID],
        u.[Name],
        u.[Team],
        FORMAT(qs.[Total] / ws.[Total], 'P') as [%]

FROM        [Users]     AS  u
LEFT JOIN   WorkSums    AS  ws  ON  ws.[UserID] = u.[UserID]
LEFT JOIN   QualitySums AS  qs  ON  qs.[UserID] = u.[UserID]

Upvotes: 3

Related Questions