Search code examples
sql-serverssms-16

GROUP BY T1.* ? Group by all columns in Table1, joined left by table 2, and Aggregate functions on T2 columns?

I have a query that is merging 2 tables. Table 1 has many columns, and may eventually expand. Table 2 also has several columns, but I will be performing aggregate functions on 90% of its columns. Table 1 has 300 + rows, Table 2 has 84K + rows.

SELECT 
     t1.*
    ,t2.c2
    ,SUM(t2.c3)
    ,SUM(t2.c4)
FROM 
    Table1 AS t1 
    LEFT JOIN Table2 AS t2 ON t1.c10 = t2.c1
GROUP BY 
     t1.* 
    ,t2.c2

I'm getting an error Incorrect Syntax near '*' and it points to the line containing the GROUP BY statement.

I am aware that the SELECT t1.* works as I ran this portion prior to trying to aggregate T2 columns and it worked as expected.

Is there a way to quickly GROUP BY all the columns in T1? I know normally we would select only needed columns, but in this case, I need all the T1 columns.

Previous research has led me to only find instances where 1 table was used, and mostly people were looking to get or remove duplicate values. I'm looking to specifically combine the 300 records of T1 to the 84K records of T2 without having to name off all the columns from T1 in the GROUP BY section.

Solution

  • You can't use * in GroupBy Statement. Of course, there are some Dynamic SQL to prevent typing all columns in the SP but if you are using T-SQL in a view you should type all columns.