1

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.

underscore_d
  • 6,309
  • 3
  • 38
  • 64
MartyMcfly0033
  • 168
  • 11
  • 1
    Have you looked at this answer: https://dba.stackexchange.com/questions/21226/why-do-wildcards-in-group-by-statements-not-work – wosi Jan 09 '20 at 17:00
  • I had not seen that article. This helped in lowering the row count, but I'm still at around 70K and T1 data is still being duplicated. – MartyMcfly0033 Jan 09 '20 at 17:18
  • I would recommend [GROUPING SETS](https://learn.microsoft.com/en-us/sql/t-sql/functions/grouping-transact-sql), but they can't be applied in your case since you refer to one column of second table. – JohnyL Jan 09 '20 at 17:23
  • Actually, @wosi's answer works for me.The reason I was getting the 70K rows when joining the tables is because I needed to join them on 2 columns instead of one. Thanks for the help everyone. – MartyMcfly0033 Jan 09 '20 at 17:31

3 Answers3

0

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.

XAMT
  • 1,515
  • 2
  • 11
  • 31
  • If I can use * in select then, is it possible to assign t1.* to a variable? Would that work? – MartyMcfly0033 Jan 09 '20 at 16:57
  • No, you can't assign * to any System Data Type variable or User Defined Table Type. You can assign * to variable just if one column is in *. – XAMT Jan 09 '20 at 17:09
  • Not the answer I was looking for but thank you for the additional info. I'll know for future reference. – MartyMcfly0033 Jan 09 '20 at 17:35
0

This method is slightly unconventional, but you can pass it into a variable by using dynamic sql. Below is an example of how you can do it:

declare @test nvarchar(max)
set @test = ''
select @test += Column_name +',' from information_schema.columns where table_name='Table1'
DECLARE @sql nvarchar(max)
SELECT @sql = N'SELECT top 10 ' +@test+ 'NULL as a FROM Table1;'
EXEC sp_executesql @sql

You can apply the same principle and rewrite your query to use the group by function. Hope this helps.

CR7SMS
  • 2,520
  • 1
  • 5
  • 13
0

Based on the article posted by @wosi, https://dba.stackexchange.com/questions/21226/why-do-wildcards-in-group-by-statements-not-work, I was able to modify the code and get the expected results. Please note I went from 80K to 70K because I was joining the tables on 1 column. The way my data was structured I had to join on 2 columns. Final code looks something like this:

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