Best way to GROUP BY id while SELECTing a description field without including it in the GROUP BY cla-CodePudding

I have two tables:

Company Table

Id,
CompanyName

Sale Table:

Id,
CompanyId,
Date,
Value

I need to group sales by date and by company and display it like this:

CompanyId  CompanyName           Date        Value
---------  --------------------  ----------  --------
        1  Company 1             2021-10-01  2832.98
        2  Company 2             2021-10-01  8112.81
        1  Company 1             2021-10-02  2182.12
        2  Company 2             2021-10-02  3521.11

I know I can easily achieve it with this

SELECT s.CompanyId, c.CompanyName, s.Date, SUM(s.Value) FROM Sale s
JOIN Company c on s.CompanyId = c.Id
GROUP BY s.Date, s.CompanyId, c.CompanyName

The problem is that when I include CompanyName in the GROUP BY clause, it gets an order of magnitude slower. I may be able to circunvent it by adding a new index, but that's not an option right now.

I was able to make it run faster using this query

SELECT 
    s.CompanyId, 
    (SELECT CompanyName FROM Company WHERE Id = s.CompanyId) as CompanyName
    s.Date, 
    SUM(s.Value) 
FROM Sale s
GROUP BY s.Date, s.CompanyId

But I want to know if there's a faster/better method. The group by without the CompanyName runs in 2 seconds, If I add CompanyName to the GROUP BY it adds 20 seconds to the query. Using the last query I listed, it runs in around 5 seconds.

CodePudding user response：

defer the lookup of name until after the grouping? Not tested...

select i.CompanyId,c.CompanyName,i.Date,i.Value
From Company c join(
SELECT 
    s.CompanyId, 
    s.Date, 
    SUM(s.Value) as [Value]
FROM Sale s
GROUP BY s.Date, s.CompanyId) i
on i.CompanyId = c.CompanyId