sql server - Filter and then aggregate two datasets in SQL

Question

I have two datasets, df1 and df2, where I would like to perform a join, set a filter and then perform an aggregate.

df1

version host    date                                      
pat     a16     12/1/2019   
fam     a16     12/1/2019   
emp     a16     12/1/2019   
dan     a16     12/1/2019 

df2

name    purpose  date
pat     hi       12/1/2019
fam     cat      12/1/2019
hello   dog      12/1/2019
dan     bird     12/1/2019

Here are the join results:

version host    date                 name    purpose       date                      
pat     a16     12/1/2019            pat     hi            12/1/2019
fam     a16     12/1/2019            fam     cat           12/1/2019

DESIRED

version    host    date             name      purpose   date
2          a16     12/1/2019        2         2         12/1/2019

DOING

    select    count(df1.version), df1.host, df1.date, count(df2.name),count(df2.purpose), df2.date
    from      df1
    left join df2
    on        df1.version = df2.name  AND
              df1.date    = df2.date
    where     df2.purpose = 'hi' OR df2.purpose = 'cat'
    group by  df1.host

However, I am not getting the desired result with a count for the desired columns. Any suggestion is appreciated.

question from:https://stackoverflow.com/questions/65837069/filter-and-then-aggregate-two-datasets-in-sql

Answer 1

Adding another two group by clauses should fix.

Try GROUP BY df1.host, df2.date, df1.date