CODEWITHSUNDEEP
sqloracle-databaseplsql
LeeMo
LeeMo

Reputation: 13

Need to count amount of "Customers" within a certain range of "headquarters" and also show as a percentage

This is my current query however I'm unsure why I'm getting an error (error below query)

SELECT
    DISTINCT COUNT(OperatorAU.*) AS "Total Operators",
100 * (COUNT(OperatorAU.UnderAU) / COUNT(OperatorAU.*) AS "Operators Within 3 AU (%)"
FROM
(
    SELECT DISTINCT 
        Operators.DestionationID AS DestinationID,
        Operators.OperatorName AS OperatorName,
        (ABS(Destinations.SolarCoordinateX) / 5) + (ABS(Destinations.SolarCoordinateY) / 5) AS DistanceInAU,
        CASE
            WHEN (ABS(Destinations.SolarCoordinateX) / 5) + (ABS(Destinations.SolarCoordinateY) / 5) < 3 THEN 1
            ELSE 0
        END AS UnderAU
    FROM Operators
    INNER JOIN Destinations ON Operators.DestinationID = Destinations.DestinationID
    ORDER BY DistanceInAU ASC
) AS OperatorAU;

Error

Error at Command Line : 2 Column : 31 Error report - SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification 01747. 00000 - "invalid user.table.column, table.column, or column specification" *Cause:
*Action:

This is the result I'm looking for, thank you in advance.

Total Operators Operators Within 3 AU Percentage Within 3 AU
100 10 10%

I have double checked all ID's and they all match my table.

Upvotes: 0

Views: 46

Answers (1)

d r
d r

Reputation: 7786

The error was caused by alias "Operators Within 3 AU (%)" whose name is not according to the rules and there is one right bracket missing and ...
Anyway - Try it like this:

SELECT
    COUNT(*) AS "Total_Operators",
    100 * (COUNT(UnderAU) / COUNT(*)) AS "Operators_Within_3_AU_PCT"
FROM
    (
        SELECT  
            Operators.DESTINATION_ID AS DestinationID,
            Operators.OPERATOR_NAME AS OPERATOR_NAME,
            (ABS(Destinations.SOLAR_COORDINATE_X) / 5) + (ABS(Destinations.SOLAR_COORDINATE_Y) / 5) AS DistanceInAU,
            CASE
                WHEN (ABS(Destinations.SOLAR_COORDINATE_X) / 5) + (ABS(Destinations.SOLAR_COORDINATE_Y) / 5) < 3 THEN 1
                ELSE 0
            END AS UnderAU
        FROM Operators
        INNER JOIN Destinations ON Operators.Destination_ID = Destinations.Destination_ID
        ORDER BY DistanceInAU ASC
    )

With sample data like here:

WITH
    operators AS
        (
            Select 1 "ID", 'Name 1' "OPERATOR_NAME", 101 "DESTINATION_ID", 'xxx' "SOME OTHER_COLUMN" From Dual Union All
            Select 2 "ID", 'Name 2' "OPERATOR_NAME", 102 "DESTINATION_ID", 'xxx' "SOME OTHER_COLUMN" From Dual Union All
            Select 3 "ID", 'Name 3' "OPERATOR_NAME", 103 "DESTINATION_ID", 'xxx' "SOME OTHER_COLUMN" From Dual 
        ),
    destinations AS
        (
            Select 101 "DESTINATION_ID", 1 "SOLAR_COORDINATE_X", 1.5 "SOLAR_COORDINATE_Y" From Dual Union All
            Select 102 "DESTINATION_ID", 1 "SOLAR_COORDINATE_X", 1.7 "SOLAR_COORDINATE_Y" From Dual Union All
            Select 101 "DESTINATION_ID", 2 "SOLAR_COORDINATE_X", 1.5 "SOLAR_COORDINATE_Y" From Dual Union All
            Select 102 "DESTINATION_ID", 1 "SOLAR_COORDINATE_X", 2   "SOLAR_COORDINATE_Y" From Dual Union All
            Select 103 "DESTINATION_ID", 1 "SOLAR_COORDINATE_X", 2   "SOLAR_COORDINATE_Y" From Dual
        )

... the result would be:

Total_Operators Operators_Within_3_AU_PCT
5 100

The sample data doesn't make sense and they are here just to show some result without errors - you should adjust the code to your needs. You showed us the expected result but not the sample data from which the result is expected...
NOTE: Distinct keyword could be performance costly with bigger datasets - so, don't use it if there is no need.

Upvotes: 0

Related Questions