CODEWITHSUNDEEP
sqloracle-databasecountinner-joinoracle-apex
CodingHurtsMyHead
CodingHurtsMyHead

Reputation: 19

What expression is missing in this sql command?

I put in this SQL command and it tells me I am missing an expression. What is it?

SELECT Warehouse_Number, DISTINCT COUNT(DISTINCT Order_Number) AS NumberOfOrders
FROM Part, Order_Line
WHERE Part.Part_Number=Order_Line.Part_Number
GROUP BY Warehouse_Number;

Upvotes: 0

Views: 57

Answers (3)

Serge de Gosson de Varennes
Serge de Gosson de Varennes

Reputation: 11504

I think you meant

SELECT Warehouse_Number, 
COUNT(DISTINCT Order_Number) AS NumberOfOrders
FROM Part as P
join Order_Line as OL  --Possible inner join
on P.<some variable> = OL.<some variable> --Hopefully a key between variables
WHERE Part.Part_Number=Order_Line.Part_Number
GROUP BY Warehouse_Number;

Upvotes: 0

GMB
GMB

Reputation: 222542

Consider:

select p.warehouse_number, count(distinct ol.order_number) as numberoforders
from part p
inner join order_line ol on ol.part_number = p.part_number
group by p.warehouse_number;

That is:

  • use distinct only once, within aggregate function count(); the other way to use that keyword is to put it directly after select (as in select distinct ...), but this does not make sense for an aggregation query to start with

  • use standard joins! Implicit joins (with commas in the from clause and join conditions in the where clause) are archaic syntax, that should not be used in new code

  • table aliases make the query easier to write and read

  • in a multi-table query, all columns should be qualified with the (alias of the) table they belong to - I assumed that warehouse_number comes from part

Upvotes: 1

Mureinik
Mureinik

Reputation: 311723

The distinct modifier can only appear at the beginning of the select list or inside an aggregate function, not before a non-first select element.

Since you're using a group by clause you don't need it anyway - just drop it, and you should be OK:

SELECT Warehouse_Number, COUNT(DISTINCT Order_Number) AS NumberOfOrders
FROM Part, Order_Line
WHERE Part.Part_Number=Order_Line.Part_Number
GROUP BY Warehouse_Number;

Upvotes: 0

Related Questions