1

I don't have schema of my table unfortunately. The table has the following data.

enter image description here

I can run order by query but not group by query on this table

Works

select node_name, projection_name, row_count, used_bytes,ros_row_count, ros_used_bytes,ros_count 
from projection_storage 
order by projection_name;

Doesn't work and gives error Column "projection_storage.node_name" must appear in the GROUP BY clause or be used in an aggregate function

select node_name, projection_name, row_count, used_bytes,ros_row_count, ros_used_bytes,ros_count 
from projection_storage 
group by projection_name;

Is it that Group By the selected columns need to be in an aggregate function (COUNT, MAX, MIN, SUM, AVG) and also that they need to be of type number (no string, blobs etc).

Manu Chadha
  • 15,555
  • 19
  • 91
  • 184

3 Answers3

3

Does Group by always need an aggregate function?

In an aggregation query, the unaggregated expressions in the SELECT need to be consistent with the expressions in the GROUP BY. All other expressions need to use aggregation functions.

A GROUP BY query does not have to have any aggregation functions in the SELECT.

The most common type of consistency is that the unaggregated expressions are exactly the same. However, the SQL Standard also supports:

  • Expressions based on the expressions in the GROUP BY (and constants).
  • GROUP BY keys that are missing from the SELECT.
  • Any column from a table where the primary key or unique key is in the GROUP BY.

The third involves a concept called "functional dependence" and most databases do not (yet) support this functionality.

If you have a question about how to do something in particular, then ask a new question. Provide sample data, desired results, an explanation of what you want to do, and an appropriate database tag.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

If you specify the GROUP BY clause, columns referenced must be all the columns in the SELECT clause that do not contain an aggregate function.

Refer here for detailed explanation.

Shradha
  • 2,232
  • 1
  • 14
  • 26
0

the answer is yes, you need always an aggregate function if you use a Group By statement.

As W3C says,

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

In my experience, I have never seen a Group By function without the aggregate function statement applied.

Here a link on Stackoverflow about the aggregate function.

Alessandro
  • 337
  • 1
  • 5
  • 18