Avoiding GROUP BY on specific columns

Question

I am looking at avoiding grouping by specific columns in a large query to reduce performance problems.

Example: http://sqlfiddle.com/#!18/cb98e/2

CREATE TABLE [OrderTable] 
(
    [id] INT,
    [OrderGroupID] INT,
    [Total] INT,
    [fkPerson] INT,
    [fkitem] INT,

    PRIMARY KEY (id)
) 

INSERT INTO [OrderTable] (id, OrderGroupID, Total, [fkPerson], [fkItem]) 
VALUES ('1', '1', '20', '1', '1'),
       ('2', '1', '45', '2', '2'),
       ('3', '2', '32', '1', '1'),
       ('4', '2', '30', '2', '2');

CREATE TABLE [Person] 
(
    [id] INT, 
    [Name] VARCHAR(32),
    PRIMARY KEY (id)
) 

INSERT INTO [Person] (id, Name) 
VALUES ('1', 'Fred'),
       ('2', 'Sam');

CREATE TABLE [Item] 
(
    [id] INT,
    [ItemNo] VARCHAR(32),
    [Price] INT,
    PRIMARY KEY (id)
) 

INSERT INTO [Item] (id, ItemNo, Price) 
VALUES ('1', '453', '23'),
       ('2', '657', '34');

Query:

WITH TABLE1 AS 
(
    SELECT 
        P.ID AS [PersonID], 
        P.Name, 
        SUM(OT.[Total]) AS [Total], 
        i.[id] AS [ItemID],
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
        ot.fkperson
    FROM 
        OrderTable OT
    INNER JOIN 
        Person P ON P.ID = OT.fkperson
    INNER JOIN 
        Item I ON I.[id] = OT.[fkItem]
    GROUP BY 
        P.ID, P.Name, i.id, ot.fkperson
)
SELECT 
    *,
    Totalrows = (SELECT MAX(rownum) FROM TABLE1)
FROM 
    TABLE1

Result:

| PersonID | Name | Total | ItemID | rownum | fkperson | Totalrows |
+----------+------+-------+--------+--------+----------+-----------+
|        1 | Fred |    52 |      1 |      1 |        1 |         2 |
|        2 |  Sam |    75 |      2 |      2 |        2 |         2 |

Now, for example if i didn't want to GROUP BY a varchar column (Person Name) i could do this - remove person table and join it back later. e.g.

WITH TABLE1 AS 
(
    SELECT 
        -- P.ID AS [PersonID], 
        -- P.Name, 
        SUM(OT.[Total]) AS [Total], 
        i.[id] AS [ItemID],
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
        ot.fkperson
    FROM 
        OrderTable OT
    -- INNER JOIN Person P ON P.ID = OT.fkperson
    INNER JOIN 
        Item I ON I.[id] = OT.[fkItem]
    GROUP BY 
        -- P.ID, P.Name,
        i.id, ot.fkperson
)
SELECT 
    p.id as [PersonID],
    p.Name,
    t1.[total],
    t1.[itemid],
    t1.[rownum],
    t1.fkperson
    -- Totalrows = (SELECT MAX(rownum) FROM TABLE1 GROUP BY
    --    i.id
    --  ,ot.fkperson        
          --   )
FROM 
    TABLE1 T1
INNER JOIN 
    Person P ON P.ID = T1.fkperson

Result:

| PersonID | Name | total | itemid | rownum | fkperson |
+----------+------+-------+--------+--------+----------+
|        1 | Fred |    52 |      1 |      1 |        1 |
|        2 |  Sam |    75 |      2 |      2 |        2 |

My issue is I also want to include the MAX(rownum) column but how can I do this in my last query without having to group by everything again? What's the best approach to this? Have I missed something really obvious? :)

user_8500110 · Answer

You can also use CROSS APPLY. And count for the total you can use COUNT(*) OVER()

SELECT 
    P.id as [PersonID],
    P.Name,
    T1.[total],
    I.id as [itemid],
    ROW_NUMBER() OVER (
            ORDER BY (SELECT NULL)
            ) AS [rownum],
    T1.fkperson,
    COUNT(*) OVER () Totalrows 
FROM 
    Item I
    CROSS APPLY (SELECT ot.fkperson, SUM(OT.[Total]) AS [total] 
                 FROM OrderTable OT 
                 WHERE I.[id] = OT.[fkItem] 
                 GROUP BY ot.fkperson ) AS T1
    INNER JOIN Person P ON P.id = t1.fkperson

Result:

PersonID    Name        total       itemid      rownum    fkperson    Totalrows
----------- ----------- ----------- ----------- --------- ----------- -----------
1           Fred        52          1           1         1           2
2           Sam         75          2           2         2           2

Avoiding GROUP BY on specific columns

1 Answers