Grouping by all non-aggregated columns in MSSQL

Question

Suppose I have two tables PO and PO_Line and I want to list all fields from PO plus the quantity of rows from PO_Line that link back to each row in PO I would write a query something like -

SELECT
  PO.propA,
  PO.propB,
  PO.propC,
  PO.propD,
  PO.propE,
  ...
  PO.propY,
  COUNT(PO_Line.propA) LINES
FROM
  PO
LEFT JOIN
  PO_Lines
ON
  PO.ID = PO_Lines.PO_ID

Obviously this would give an error someting along the lines of -

Column 'PO.propA' is invaalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So to get the query to run I will add a GROUP BY clause to the end of the query and copy and paste my select lines, like so -

SELECT
  PO.propA,
  PO.propB,
  PO.propC,
  PO.propD,
  PO.propE,
  ...
  PO.propY,
  COUNT(PO_Line.propA) LINES
FROM
  PO
LEFT JOIN
  PO_Lines
ON
  PO.ID = PO_Lines.PO_ID
GROUP BY
  PO.propA,
  PO.propB,
  PO.propC,
  PO.propD,
  PO.propE,
  ...
  PO.propY

Which works perfectly however it all feels a little unwieldy, especially if I've named my columns i.e. -

SELECT
  PO.propA AS 'FIRST PROPERTY',
  PO.propB AS 'SECOND PROPERTY',
  PO.propC AS 'THIRD PROPERTY',
  PO.propD AS 'ANOTHER PROPERTY',
  PO.propE AS 'YOU GET THE IDEA',
  ...
  PO.propY

and I have to copy/paste the entries from the select clause and then delete the column names.

So my question is - Is there a shorthand method to say group by all non-aggregated entries found in the select clause?

You can do a SELECT on just the aggregate columns and the non-aggregate key fields, and then JOIN to another SELECT DISTINCT of the rest of the non-aggregate fields. This is also unwieldy, but it has the advantage of allowing you to do SELECT *, rather than explicitly listing all of the non-aggregate fields. — mbeckish, Apr 07 '16 at 12:53

score 2 · Accepted Answer · answered Apr 07 '16 at 12:27

2

I think you just want window functions:

SELECT . . .,
       COUNT(PO_Line.propA) OVER (PARTITION BY PO.ID) as LINES
FROM PO LEFT JOIN
     PO_Lines
     ON PO.ID = PO_Lines.PO_ID;

answered Apr 07 '16 at 12:27

Gordon Linoff

1,242,037
58
646
786

1

This might return a lot of duplicated rows assuming that the select is only getting columns from `PO`. – Lucero Apr 07 '16 at 12:51

score 1 · Answer 2 · edited May 23 '17 at 11:45

1

I have to copy/paste the entries from the select clause and then delete the column names.

I strongly suggest to use block/column selection.Move , as first element and allign your aliases:

SELECT
   PO.propA AS 'FIRST PROPERTY'
  ,PO.propB AS 'SECOND PROPERTY'
  ,PO.propC AS 'THIRD PROPERTY'
  ,PO.propD AS 'ANOTHER PROPERTY'
  ,PO.propE AS 'YOU GET THE IDEA'
  ...
  ,PO.propY
  ,COUNT(PO_Line.propA) LINES
FROM  PO
LEFT JOIN  PO_Lines
  ON  PO.ID = PO_Lines.PO_ID
GROUP BY
   ...

In SQL Server Management Studio use simple block selection SHIFT + ALT highlight and paste.

If you are using other editor find corresponding keyshortcut here.

It is nice for multiple edit at once like adding schema, alias, ...and so on.

edited May 23 '17 at 11:45

Community

1
1

answered Apr 07 '16 at 12:33

Lukasz Szozda

162,964
23
234
275

Most halfway capable editors (at least on Windows) use Alt for block selection; this even works in MS Word! ;) – Lucero Apr 07 '16 at 12:44
1

Still a little awkward as you need to format your `SELECT` clause so that when you have fields with differing length names they all need to line up but saying that I have learned something new! – John C Apr 07 '16 at 15:32
@JohnC That is why you should use tools like [`Redgate`](http://www.red-gate.com/assets/INTERIM/assets/products/sql-prompt/images/one-click-sql-formatting.png) or `Apex` that will do it for you. – Lukasz Szozda Apr 07 '16 at 15:37

score 1 · Answer 3 · answered Apr 07 '16 at 12:49

1

Reading your query I think you might not need a GROUP BY to begin with:

SELECT
  PO.propA,
  PO.propB,
  PO.propC,
  PO.propD,
  PO.propE,
  ...
  PO.propY,
  (SELECT COUNT(*) FROM PO_Lines WHERE PO.ID = PO_Lines.PO_ID) LINES
FROM
  PO

answered Apr 07 '16 at 12:49

Lucero

59,176
9
122
152

Grouping by all non-aggregated columns in MSSQL

3 Answers3