-3

I have a table, say, A with 100 columns. I am trying to join another table B to this, which has certain measures which need to be aggregated to the level of the first table, which means I need to group by all columns in table A (Group by A.'asterisk' (I am not able to specify the asterisk here). And I don't want to obviously list down each of the 100 column names. Currently, if I try group by A.* in SQL Server, I get an error saying "incorrect syntax near '*' " and have to specify each column name. So, is there a way to avoid listing all the column names?

Mohan Prashanth
  • 11
  • 3
  • You have to explicitly list the fields you are grouping by in your group by. If you add it to the select, it has to go into the group by. – dfundako Jul 31 '19 at 15:33
  • 3
    If your question is about `group by *` then no you can't do it and it would not make sense. – forpas Jul 31 '19 at 15:33
  • Hi @forpas could you tell me why it wouldn't make sense? – Mohan Prashanth Jul 31 '19 at 15:37
  • 4
    @MohanPrashanth If you have something like SELECT id, COUNT(*) .... GROUP BY *, the * represents all/everything. You don't want to group by everything, you want to group by ID only. The * would encompass the aggregated field as well, which would not make sense and would be incorrect. – dfundako Jul 31 '19 at 15:40
  • Do you know the use of `group by somecolumn`? It creates groups of all the rows of the table and each grouphas common the column `somecolumn`. When you do `group by *` what groups would you create? – forpas Jul 31 '19 at 15:41
  • It sounds like you are asking how to remove duplicates from a table. Check out this thread: https://stackoverflow.com/q/18390574/11736511 – B0RDERS Jul 31 '19 at 15:48
  • 1
    Yes, please update the post with some sample data and what you expect for results. It is not clear what you are trying to do here. – Jacob H Jul 31 '19 at 15:54

1 Answers1

3

No. there is not. Grouping by every column in a table would negate whatever aggregation you'd be trying to achieve. And if you're trying to just group by all columns in the select list minus the aggregate column, you need to explicitly list them.

Edit: With the updated question, the answer would be to query table B joined only to the necessary columns in Table A to aggregate, aggregate and group that to your heart's content, then join that resultset to the rest of table A as a subquery.

ex: (NOTE: The *'s are only there for the sake of not typing a ton of dummy fields. I wouldn't recommend SELECT * in production code.)

SELECT A.*
     , Agg.*
FROM TableA A
INNER JOIN 
(
   SELECT tblA.Col1
          tblA.Col2
          SUM(tblB.Col1)
   FROM TableA tblA
   INNER JOIN TableB tblB ON ***JOIN CONDITION***
   GROUP BY tblA.Col1, tblA.col2
) AS AGG ON ***JOIN CONDITION***
BeardOfTriumph
  • 472
  • 2
  • 8
  • 1
    Thank you, @BeardOfTriumph. But I have edited the question to clearly convey what I was facing - my earlier version would have come across as trying to aggregate everything in the same table and I understand that is nonsensical - but yea, I actually want to group by A.*, where A is one of the tables I am trying to join – Mohan Prashanth Jul 31 '19 at 16:50
  • Sure, thanks for the answer! And yea, I have tried this. But was looking along the lines of a simpler, 1-step solution (I am considering the sub query as a separate step) , which I don't think is possible, going by the answers – Mohan Prashanth Jul 31 '19 at 17:47
  • 1
    Have you tried doing it with a windowing function using the OVER keyword? – BeardOfTriumph Jul 31 '19 at 17:48