How to write group by query in sql widget?
Question

Is this correct in Sql widget?if it is then why it throw me an error.How would resolve it? 

mvp_badge
MVP

Hello there Karthick,

Hope you're doing well.


When you use a Group By clause in an SQL statement, you have to include the attribute from the Group By in the Select.

Also, this clause is used alongside with the aggregate function that you want to use (for example Count, Max, Min, Sum, Avg).


As an example, if you want to count the different rooms with the same price, you may do something like:


Select {Room}.Price, Count(1)

From {Room}

Group By {Room}.Price


The SQL statement depends on what you want to achieve.

Hope that this helps you!


Kind regards,

Rui Barradas

Champion

Hi Karthick M,

You need to understand how Group by works in SQL(not only in Outsystems).

Only group by column or aggregate expression can appear in select Claus.

Please refer to this link.

Kind regards


Hello, Karthick.

I believe you also should know

SELECT * FROM {Room};

will not work even without GROUP BY but

SELECT {Room}.* FROM {Room};

will work.

Also, non grouped attributes need to be sealed by other functions such as SUM, MIN, COUNT... to avoid errors.

Please, find the details following by the link Tom Provided (That is a good one).

I believe you also would need the correct output structure at the end.

Best regards

Hi Karthick,

Below are statements for Group By clause:

  • SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY statement is used with the SQL SELECT statement.
  • The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
  • The GROUP BY statement is used with aggregation function.

Syntax:

SELECT column  

FROM table_name  

WHERE conditions   

GROUP BY column  

ORDER BY column  

Example:

SELECT COMPANY, COUNT(*)  

FROM PRODUCT_MAST   

GROUP BY COMPANY;  

Hi @Karthick M 

In addition, you can check this course in order to understand better SQL queries

https://www.outsystems.com/training/courses/146/sql-queries/

Regards,

Gonçalo Almeida

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.