SQL Group by Count and Date-CodePudding

I have two tables

Table 1:

'table1Id' int,
'date' date

Table 2:

'table2Id' int,
'table1Id' int,
'switchVal' int DEFAULT NULL,
FOREIGN KEY 'table1Id' REFERENCES table1('table1Id')

How do I go about displaying the count table1Id grouped by date assuming that switchVal is not null?

CodePudding user response：

Something like this:

SELECT        SUM(dbo.Table_1.table1Id) AS Expr1, dbo.Table_2.switch1val, dbo.Table_1.date
FROM            dbo.Table_1 INNER JOIN
                         dbo.Table_2 ON dbo.Table_1.table1Id = dbo.Table_2.table1id
GROUP BY dbo.Table_2.switch1val, dbo.Table_1.date
HAVING        (dbo.Table_2.switch1val IS NOT NULL)

CodePudding user response：

You need to join the tables and group by based on the column table1Id.

Select t1.date, count(t1.table1Id) as 'Count' from Table1 t1 inner join Table2 t2 on t1.table1Id = t2.table1Id
where t2.[switchVal] is not null
group by t1.date