CODEWITHSUNDEEP
sqloracle-database
Christopher Jack
Christopher Jack

Reputation: 97

Group by in oracle is not working using max

I am having issues trying to get my group by to work in Oracle.

My script is as follows

Select
    DISTINCT dc.despatch_num as ShipmentReference,
    sh.delivery_name as Name,
    dc.address1 as DeliveryAddress1,
    dc.address2 as DeliveryAddress2,
    dc.address3 as City,
    dc.address5 as County,
    dc.POSTCODE as Postcode,
    dc.country_code as Country,
    sh.inv_contact_telephone_1 as TelephoneNumber,
    sh.inv_contact_email as email,
    ph.package_num as Numberofpackages,
    ph.package_id as PackageReference,
    ph.weight as PackageWeight,
    'Kilos' as WeightUnit,
    max(pro.length) as Length
from 
    sales_header sh
    left join customer cu on sh.customer_account = cu.customer_account
    left join sales_item si on sh.sales_document_num = si.sales_document_num
    left join customer_address ca on cu.customer_account = ca.customer_account
    left join additional_charge ac on sh.sales_document_num = ac.sales_document_num and sh.order_type = ac.order_type
    left join despatch_header dc on ac.despatch_num = dc.despatch_num
    left join package_header ph on dc.despatch_num = ph.despatch_num
    left join product pro on si.product_code = pro.product_code
WHERE sh.customer_name <> sh.Delivery_name and rownum <= 100 and dc.country_code ='FR';
group by DISTINCT dc.despatch_num,
    sh.delivery_name,
    dc.address1,
    dc.address2,
    dc.address3,
    dc.address5,
    dc.POSTCODE,
    dc.country_code,
    sh.inv_contact_telephone_1,
    sh.inv_contact_email,
    ph.package_num,
    ph.package_id,
    ph.weight,
    'Kilos',

The error I am receiving is

**ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" Cause:
Action:

Any help appreciated.

Upvotes: 0

Views: 64

Answers (1)

Popeye
Popeye

Reputation: 35910

There are multiple issues with your code.

The following code should work for you (See inline comments for details)

Select --removed DISTINCT as it is irrelevant in GROUP BY queries
    dc.despatch_num as ShipmentReference,
    sh.delivery_name as Name,
    dc.address1 as DeliveryAddress1,
    dc.address2 as DeliveryAddress2,
    dc.address3 as City,
    dc.address5 as County,
    dc.POSTCODE as Postcode,
    dc.country_code as Country,
    sh.inv_contact_telephone_1 as TelephoneNumber,
    sh.inv_contact_email as email,
    ph.package_num as Numberofpackages,
    ph.package_id as PackageReference,
    ph.weight as PackageWeight,
    'Kilos' as WeightUnit,
    max(pro.length) as Length
from 
    sales_header sh
    left join customer cu on sh.customer_account = cu.customer_account
    left join sales_item si on sh.sales_document_num = si.sales_document_num
    left join customer_address ca on cu.customer_account = ca.customer_account
    left join additional_charge ac on sh.sales_document_num = ac.sales_document_num and sh.order_type = ac.order_type
    left join despatch_header dc on ac.despatch_num = dc.despatch_num
    left join package_header ph on dc.despatch_num = ph.despatch_num
    left join product pro on si.product_code = pro.product_code
WHERE sh.customer_name <> sh.Delivery_name 
  and rownum <= 100 
  and dc.country_code ='FR' -- removed semi-colon from here
group by dc.despatch_num, --removed DISTINCT from here as it is INVALID use
    sh.delivery_name,
    dc.address1,
    dc.address2,
    dc.address3,
    dc.address5,
    dc.POSTCODE,
    dc.country_code,
    sh.inv_contact_telephone_1,
    sh.inv_contact_email,
    ph.package_num,
    ph.package_id,
    ph.weight; --removed 'Kilos' from end

Upvotes: 2

Related Questions