CODEWITHSUNDEEP
sql-serverjoingroup-by
skywalker
skywalker

Reputation: 59

Join two queries that are grouped by same column from same tables but different parameters to join

I have 3 tables with items, owners and statuses and I need to show the count of items that were sold/discarded grouped by every owner for the year passed as parameter.

I am able to get ownername and soldcount as one query and ownername and discardcount as second query but is there a way to structure so that ownername, soldcount and discardcount come in one query?

declare @QueryYear integer = 2020
    --SOLD
    select O1.pk_owner_id,count(P1.pk_Property_ID) as [SaleCount]
    from
        Item P1, Owner O1, Status S1
    WHERE    
        (C1.fkl_owner_ID = O1.pk_owner_ID and C1.fkl_item_ID=P1.pk_item_ID and O1.isactive=1 and year(P1.dtList_Date)=@QueryYear and P1.fkl_status_ID=1)
    group by 
        O1.pk_owner_id

    --DISCARD
    select O2.pk_owner_id,count(P2.pk_item_ID) as [DiscardCount]
    from
        item P2, owner O2, status C2
    WHERE    
        (C2.fkl_Owner_ID = O2.pk_owner_ID and C2.fkl_item_ID=P2.pk_item_ID and O2.isactive=1 and year(P2.dtList_Date)=@QueryYear and P2.fkl_item_status_ID=2)
    group by 
        O2.pk_owner_id

I used a Union and it gives answer in 2 columns only.

Upvotes: 0

Views: 407

Answers (1)

pwilcox
pwilcox

Reputation: 5753

Move your status filter to case statements in your select clause.

select      o.pk_owner_id,
            SaleCount = count(case when i.fkl_status_ID = 1 then 1 end),
            DiscardCount = count(case when i.fkl_item_status_ID = 2 then 1 end)
from        Status s 
join        Item i on s.fkl_item_ID = i.pk_item_ID
join        Owner o on s.fkl_owner_ID = o.pk_owner_ID                   
where       o.isactive = 1 
and         year(i.dtList_Date) = @QueryYear 
group by    o.pk_owner_id

Also, use relational operators to express relationships between tables, don't use the where clause. In this case, because the nature of the relationship is 'horizontal' in that each row in one table matches to each row in another table, you're looking for an (inner) join.

Finally, if you have more status types than '1' and '2', then you can add another condition to your joining of status with item, or put it in your where statement. Namely, you can do something like:

and i.fkl_status_ID in (1,2)

But I notice that the status_id columns have different names for SaleCount and DiscardCount. So if that's not an error, you'll need to do a parenthesized or version. But the main point is that your query will be more efficient if the processor knows to ignore statuses that are not '1' or '2'.

Upvotes: 1

Related Questions