Group By SQL Query

[Fredek]

Hi guys,

I am getting "SQL command not properly ended" with he below query, any idea what I'm doing wrong?

SELECT 
Bal_Dte_ID,
EQ_Branch_Name,
Acct_Curr_Cde as Bal_Curr_Cde,
VALUE_BAL as SUM_BASE_BALANCE,
VALUE_BAL_USEQ as SUM_USD_BALANCE
From dwrpt.T_Risk_Merge
where  
(Bal_Dte_ID>='20180401' and bal_dte_id<='20180401')
Order by EQ_Branch_Name
Group by Acct_Curr_Cde;

 

[stumac]

Try switching the GROUP By clause and the ORDER by clause round

 

[Fredek]

Hi Stumac,

After switching them around I get a "ORA-00979: not a GROUP BY expression"...

 

[james_lankford]

"group by" is for aggregate functions like sum, count, average

https://community.modeanalytics.com/sql/tutorial/sql-group-by/

https://www.w3schools.com/sql/sql_groupby.asp

https://www.tutorialspoint.com/sql/sql-group-by.htm

 

[rondeondo]

Hi Fredek
You would need the group by only if the fields you have labelled as Sum.... are an aggregation such as sum for example:

then you need to group by the rest of the fields in the query

SELECT dwrpt_T_Risk_Merge.Bal_Dte_ID, dwrpt_T_Risk_Merge.EQ_Branch_Name, dwrpt_T_Risk_Merge.Acct_Curr_Cde AS Bal_Curr_Cde, Sum(dwrpt_T_Risk_Merge.VALUE_BAL) AS SUM_BASE_BALANCE, Sum(dwrpt_T_Risk_Merge.VALUE_BAL_USEQ) AS SUM_USD_BALANCE
FROM dwrpt_T_Risk_Merge
WHERE (((dwrpt_T_Risk_Merge.[Bal_Dte_ID])>='20180401' And (dwrpt_T_Risk_Merge.[Bal_Dte_ID])<='20180401'))
GROUP BY dwrpt_T_Risk_Merge.Bal_Dte_ID, dwrpt_T_Risk_Merge.EQ_Branch_Name, dwrpt_T_Risk_Merge.Acct_Curr_Cde;

or simplify the query as follows

SELECT 
Acct_Curr_Cde as Bal_Curr_Cde,
sum(VALUE_BAL) as SUM_BASE_BALANCE,
sum(VALUE_BAL_USEQ) as SUM_USD_BALANCE
From dwrpt.T_Risk_Merge
where  
(Bal_Dte_ID>='20180401' and bal_dte_id<='20180401')
Order by EQ_Branch_Name
Group by Acct_Curr_Cde,;

Then you can link this to a select for bringing out the branch and date.
the group by clause has to contain all of the fields that are not being aggregated as you need a total value per line of output.

so an option would be something like this:

SELECT Distinct RiskMerge.Bal_Dte_ID, RiskMerge.EQ_Branch_Name, RiskMerge.Acct_Curr_Cde AS Bal_Curr_Cde, qryBalances.SUM_BASE_BALANCE, qryBalances.SUM_USD_BALANCE
FROM dwrpt_T_Risk_Merge AS RiskMerge 
INNER JOIN 
(
SELECT dwrpt_T_Risk_Merge.Acct_Curr_Cde AS Bal_Curr_Cde, Sum(dwrpt_T_Risk_Merge.VALUE_BAL) AS SUM_BASE_BALANCE, Sum(dwrpt_T_Risk_Merge.VALUE_BAL_USEQ) AS SUM_USD_BALANCE
FROM dwrpt_T_Risk_Merge
WHERE (((dwrpt_T_Risk_Merge.[Bal_Dte_ID])>='20180401' And (dwrpt_T_Risk_Merge.[Bal_Dte_ID])<='20180401'))
GROUP BY dwrpt_T_Risk_Merge.Acct_Curr_Cde
)
qryBalances ON RiskMerge.Acct_Curr_Cde = qryBalances.Bal_Curr_Cde;

Depends how you want to use it.

Ron