Home » SQL & PL/SQL » SQL & PL/SQL » Count Distinct Not Working in Case Select Oracle SQL
Count Distinct Not Working in Case Select Oracle SQL [message #664309] |
Tue, 11 July 2017 14:02 |
|
phaenggi
Messages: 5 Registered: July 2017
|
Junior Member |
|
|
I have a PL/SQL question in which the code fails to count distinct ID's. It does count them, but does not do so distinctly. Anybody have any idea as to why?
Thank you!
SELECT
"RESERVATION_STAT_DAILY"."RESORT" AS "RESORT",
"RESERVATION_STAT_DAILY"."BUSINESS_DATE" AS "BUSINESS_DATE",
to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy') AS "MONTHYEAR",
Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "DAY",
Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "MONTH",
Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "YEAR",
"RESERVATION_STAT_DAILY"."SOURCE_CODE" AS "SOURCE_CODE",
"RESERVATION_STAT_DAILY"."MARKET_CODE" AS "MARKET_CODE",
"RESERVATION_STAT_DAILY"."RATE_CODE" AS "RATE_CODE",
"RESERVATION_STAT_DAILY"."RESV_NAME_ID" AS "RESV_NAME_ID",
(CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS'
AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA')
AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT')
THEN 'GDS'
ELSE 'Other'
END) AS "BizUnit",
COUNT(DISTINCT CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS'
AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA')
AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT')
THEN "RESERVATION_STAT_DAILY"."RESV_NAME_ID"
ELSE NULL
END) AS "COST",
(SUM("RESERVATION_STAT_DAILY"."BUSINESS_DATE" - "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED")/(COUNT ("RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED"))) AS "DIFF",
SUM(NVL("RESERVATION_STAT_DAILY"."NIGHTS",0)) AS "NIGHTS",
SUM(NVL("RESERVATION_STAT_DAILY"."ROOM_REVENUE",0)) AS "ROOM_REVENUE"
FROM "OPERA"."RESERVATION_STAT_DAILY" "RESERVATION_STAT_DAILY"
Where RESORT in ('558339','558341','4856','558340','602836','HCA','HZSD', 'TAC') and
BUSINESS_DATE < SYSDATE AND EXTRACT(year FROM "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED") >=2016
GROUP BY
"RESERVATION_STAT_DAILY"."RESORT",
"RESERVATION_STAT_DAILY"."BUSINESS_DATE",
to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy'),
Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"),
Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"),
Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"),
"RESERVATION_STAT_DAILY"."SOURCE_CODE",
"RESERVATION_STAT_DAILY"."MARKET_CODE",
"RESERVATION_STAT_DAILY"."RATE_CODE",
"RESERVATION_STAT_DAILY"."RESV_NAME_ID",
( CASE
WHEN (("RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS') AND ("RESERVATION_STAT_DAILY"."RATE_CODE" != 'BKIT' OR "RESERVATION_STAT_DAILY"."RATE_CODE" != 'EXPEDIA'
)) THEN 'GDS'
ELSE 'Other'
END )
|
|
|
|
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664331 is a reply to message #664311] |
Wed, 12 July 2017 03:27 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Count distinct is too basic to make mistakes with so either:
a) you're looking at an oracle bug - in which case you need to contact oracle support
b) it is doing what it's supposed to do and there's a flaw in whatever method you are using to check the result.
b is much, much more likely.
So how are you determining that it's wrong?
|
|
|
|
Re: Count Distinct Not Working in Case Select Oracle SQL [message #664346 is a reply to message #664332] |
Wed, 12 July 2017 07:48 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I formatted your code for you
SELECT Rsd.Resort AS Resort,
Rsd.Business_date AS Business_date,
TO_CHAR (Rsd.Business_date, 'MON-yyyy') AS Monthyear,
EXTRACT (DAY FROM Rsd.Business_date) AS Day,
EXTRACT (MONTH FROM Rsd.Business_date) AS Month,
EXTRACT (YEAR FROM Rsd.Business_date) AS Year,
Rsd.Source_code AS Source_code,
Rsd.Market_code AS Market_code,
Rsd.Rate_code AS Rate_code,
Rsd.Resv_name_id AS Resv_name_id,
(CASE
WHEN Rsd.Source_code = 'GDS'
AND Rsd.Rate_code NOT IN ('BKIT', 'EXPEDIA')
AND Rsd.Market_code NOT IN ('GOVG', 'ENT')
THEN
'GDS'
ELSE
'Other'
END)
AS Bizunit,
COUNT (
DISTINCT CASE
WHEN Rsd.Source_code = 'GDS'
AND Rsd.Rate_code NOT IN ('BKIT', 'EXPEDIA')
AND Rsd.Market_code NOT IN ('GOVG', 'ENT')
THEN
Rsd.Resv_name_id
ELSE
NULL
END)
AS Cost,
( SUM (Rsd.Business_date - Rsd.Business_date_created)
/ (COUNT (Rsd.Business_date_created)))
AS Diff,
SUM (NVL (Rsd.Nights, 0)) AS Nights,
SUM (NVL (Rsd.Room_revenue, 0)) AS Room_revenue
FROM Opera.Reservation_stat_daily Rsd
WHERE Resort IN ('558339',
'558341',
'4856',
'558340',
'602836',
'HCA',
'HZSD',
'TAC')
AND Business_date < SYSDATE
AND EXTRACT (YEAR FROM Rsd.Business_date_created) >= 2016
GROUP BY Rsd.Resort,
Rsd.Business_date,
TO_CHAR (Rsd.Business_date, 'MON-yyyy'),
EXTRACT (DAY FROM Rsd.Business_date),
EXTRACT (MONTH FROM Rsd.Business_date),
EXTRACT (YEAR FROM Rsd.Business_date),
Rsd.Source_code,
Rsd.Market_code,
Rsd.Rate_code,
Rsd.Resv_name_id,
(CASE
WHEN ( (Rsd.Source_code = 'GDS')
AND ( Rsd.Rate_code != 'BKIT'
OR Rsd.Rate_code != 'EXPEDIA'))
THEN
'GDS'
ELSE
'Other'
END)
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:13:45 CDT 2024
|