How to count total occurrence from one table with another table-CodePudding

Table 1:

ID      Name    Type    Start       End
90      TR1092  A       02-12-2019  02-12-2019
120     TR1096  A       02-16-2019  02-16-2019
101     TR1096  A       02-22-2019  02-22-2019
34      TR1092  A       02-22-2019  02-22-2019
1       DR9209  A       02-12-2019  02-12-2019
20      DR9209  A       02-13-2019  02-13-2019
67      DR8703  A       02-11-2019  02-11-2019
99      DR8703  A       02-11-2019  02-11-2019
145     FR2435  A       02-11-2019  02-11-2019
147     FR2345  A       02-16-2019  02-16-2019
148     FR2347  A       02-16-2019  02-16-2019

Table 2 (SessID matches with ID in Table 1):

SessID          Action
090             Push
090             Pull
090             Push
120             Push
034             Pull
034             Pull
148             Pull
148             Kick
148             Pull
020             Pull
067             Pull
067             Push
067             Punch
001             Pull
147             Kick
147             Push
148             Pull
099             Kick
099             Push

I wrote a query which gives me a total count for each Name in Table 1:

Select
    CASE
        WHEN T1.Name LIKE 'TR%' THEN 'Town Row'
        WHEN T1.Name LIKE 'DR%' THEN 'Doctor Row'
        WHEN T1.Name LIKE 'FR%' THEN 'Fruit Row'
    END AS Name
    , COUNT(*) AS 'NameTotals'
From
    NamedRecord T1
Where
    T1.Type = 'A'
    AND
    T1.Start >= DATEADD(MONTH, DATEDIFF(Month,0,Getdate(),0)
    AND
    T1.End <= DATEADD(Day,1,Getdate())
Group by
    (
        CASE
            WHEN T1.Name LIKE 'TR%' THEN 'Town Row'
            WHEN T1.Name LIKE 'DR%' THEN 'Doctor Row'
            WHEN T1.Name LIKE 'FR%' THEN 'Fruit Row'
        END
    )
Order By
    Name

Result:

Name                NameTotals
Town Row            4
Doctor Row          4
Fruit Row           3

How can I update my query so I have a column:

- `WhatHappTotals` - Count total number of actions by matching `SessID` in `Table 2` to `ID` and then to `Name` in `Table 1`

Result will look like this:

Name                NameTotals      WhatHappTotals
Town Row            4               6
Doctor Row          4               8
Fruit Row           3               6

CodePudding user response：

CREATE TABLE One (
    ID    int        NOT NULL PRIMARY KEY,
    Name  varchar(6) NOT NULL,
    Type  varchar(1) NOT NULL,
    Start datetime   NOT NULL,
    End   datetime   NOT NULL
)

CREATE TABLE Two (
    SessId int        NOT NULL PRIMARY KEY,
    Action varchar(5) NOT NULL,

    CONSTRAINT Two_FK_One FOREIGN KEY (SessId) REFERENCES One (ID)
)

SELECT Name, 
    COUNT(DISTINCT ID) AS NameCount,
    COUNT(*) AS WhatHappTotal
FROM (
    SELECT o.ID, o.Type, o.Start, o.End
        CASE 
            WHEN o.Name LIKE 'TR%' THEN 'Town Row'
            WHEN o.Name LIKE 'DR%' THEN 'TDoctor Row'
            WHEN o.Name LIKE 'FR%' THEN 'TFruit Row'
            ELSE NULL
        END AS Name,
        t.Action
    FROM One o
        INNER JOIN Two t ON t.SessId = o.ID
) T
GROUP BY Name

CodePudding user response：

SELECT
    CASE
        WHEN NR.Name LIKE 'TR%' THEN 'Town Row'
        WHEN NR.Name LIKE 'DR%' THEN 'Doctor Row'
        WHEN NR.Name LIKE 'FR%' THEN 'Fruit Row'
    END AS Name,
    COUNT(NR.ID) AS "NameTotals",
    COUNT(SI.SessID) AS "WhatHappTotals"
FROM NameRecord AS NR
JOOIN SessionId AS S1 on SI.SessID = NR.ID
    AND
    NR.Type = 'A'
    AND
    NR.Start >= DATEADD(MONTH, DATEDIFF(Month,0,Getdate(),0)
    AND
    NR.End <= DATEADD(Day,1,Getdate())
Group By Name
Order By Name;

Can you try and see if it works or not?