CODEWITHSUNDEEP
sql
user1448783
user1448783

Reputation: 263

How to use Aggregate Function within Group By clause

How to use an Aggregate Function within a GROUP BY clause like this?

SELECT VCH_DCOA_CODE, SUM(VCH_DEFINATION.VCH_CREDIT) AS Debit
FROM  VCH_DEFINATION 
GROUP BY SUBSTRING(VCH_DCOA_CODE, 0, 6)

Upvotes: 0

Views: 361

Answers (2)

Taryn
Taryn

Reputation: 247700

It looks like you want to display the sum() with the full VCH_DCOA_CODE but group by the substring value, so I am guessing that you want this:

SELECT v1.VCH_DCOA_CODE, v2.Debit
FROM VCH_DEFINATION v1
INNER JOIN
(
    select SUM(VCH_DEFINATION.VCH_CREDIT) AS Debit, SUBSTRING(VCH_DCOA_CODE, 0, 6) Shortcode
    from VCH_DEFINATION
    group by SUBSTRING(VCH_DCOA_CODE, 0, 6)
) v2
    on v1.SUBSTRING(VCH_DCOA_CODE, 0, 6) = v2.Shortcode

The subquery will get the SUM() for each SUBSTRING(VCH_DCOA_CODE, 0, 6) but then that will be displayed with each full VCH_DCOA_CODE

But, If you do not want to display the full VCH_DCOA_CODE, then you can just use the inner query to get the result:

 select SUM(VCH_DEFINATION.VCH_CREDIT) AS Debit, SUBSTRING(VCH_DCOA_CODE, 0, 6) Shortcode
 from VCH_DEFINATION
 group by SUBSTRING(VCH_DCOA_CODE, 0, 6)

Upvotes: 2

Robert
Robert

Reputation: 25753

You have to add SUBSTRING(VCH_DCOA_CODE, 0, 6) to the select clause as below:

SELECT SUBSTRING(VCH_DCOA_CODE, 0, 6), SUM(VCH_DEFINATION.VCH_CREDIT) AS Debit
FROM  VCH_DEFINATION 
GROUP BY SUBSTRING(VCH_DCOA_CODE, 0, 6)

Upvotes: 1

Related Questions