CODEWITHSUNDEEP
sqloracle-database
user3169698
user3169698

Reputation: 153

Multiple table query with count error

Whenever I add a count to the following select statement, it causes it to give me an error, and I'm not sure why:

select "WorkOrder"."WorkOrderID", "Vehicles"."Model", "Vehicles"."Color",
"Vehicles"."LicensePlate", COUNT("WorkOrderPart"."WorkPartID") AS "Parts"
FROM "WorkOrder", "Vehicles", "WorkOrderPart"
WHERE "WorkOrder"."VIN" = "Vehicles"."VIN" AND
"WorkOrder"."WorkOrderID" = "WorkOrderPart"."WorkOrderID";

Whenever I run this, I get the following error:

select "WorkOrder"."WorkOrderID", "Vehicles"."Model", "Vehicles"."Color",
       *
ERROR at line 1:
ORA-00937: not a single-group group function

What can I do about this? I am running the SQL command line with an Oracle database.

Upvotes: 0

Views: 48

Answers (1)

podiluska
podiluska

Reputation: 51504

When you have a count (or other aggregate function), you need to group by the non aggregated items

select 
     "WorkOrder"."WorkOrderID", 
     "Vehicles"."Model", 
     "Vehicles"."Color",
     "Vehicles"."LicensePlate", 
     COUNT("WorkOrderPart"."WorkPartID") AS "Parts"
FROM "WorkOrder", "Vehicles", "WorkOrderPart"
WHERE "WorkOrder"."VIN" = "Vehicles"."VIN" 
AND "WorkOrder"."WorkOrderID" = "WorkOrderPart"."WorkOrderID"
group by  "WorkOrder"."WorkOrderID", "Vehicles"."Model", "Vehicles"."Color",
"Vehicles"."LicensePlate"

As an aside, it is considered good practice to use explicit joins (unless you're using a prehistoric version of oracle) instead of the where clause

FROM "WorkOrder" 
     inner join "Vehicles" on "WorkOrder"."VIN" = "Vehicles"."VIN" 
     left join "WorkOrderPart" on "WorkOrder"."WorkOrderID" = "WorkOrderPart"."WorkOrderID"

Upvotes: 6

Related Questions