CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloperdatabase
user3045939
user3045939

Reputation: 67

Distinct doesn't work

The below code gives a wrong result. I am using DISTINCT command but type appears two times on the results.

SELECT DISTINCT 
    CONCERT.CONCERT_ID, CONCERT.C_TYPE,  
    COUNT(BOOKINGS.CUSTOMER_CUSTOMER_ID) AS NUMBER_OF_CUSTOMERS
FROM 
    CONCERT, CUSTOMER, EVENT, BOOKINGS
WHERE 
    CUSTOMER.CUSTOMER_ID =  BOOKINGS.CUSTOMER_CUSTOMER_ID
    AND EVENT.EVENT_ID =  BOOKINGS.EVENT_EVENT_ID
    AND CONCERT.CONCERT_ID =  EVENT.CONCERT_ID
GROUP BY 
    CONCERT.CONCERT_ID, CONCERT.C_TYPE
ORDER BY 
    CONCERT.CONCERT_ID DESC;

Results:

enter image description here

Upvotes: 1

Views: 12680

Answers (3)

Nick.Mc
Nick.Mc

Reputation: 19245

Do not use DISTINCT AND GROUP BY. GROUP BY already makes it distinct.

You need to tell us which of the rows 10001 or 10000 you want to keep. This one will keep the lowest one.

SELECT MAX(CONCERT.CONCERT_ID)  CONCERT_ID, CONCERT.C_TYPE,  
COUNT(BOOKINGS.CUSTOMER_CUSTOMER_ID) AS NUMBER_OF_CUSTOMERS
FROM CONCERT, CUSTOMER, EVENT, BOOKINGS
WHERE CUSTOMER.CUSTOMER_ID =  BOOKINGS.CUSTOMER_CUSTOMER_ID
AND EVENT.EVENT_ID =  BOOKINGS.EVENT_EVENT_ID
AND CONCERT.CONCERT_ID =  EVENT.CONCERT_ID
GROUP BY CONCERT.C_TYPE
ORDER BY CONCERT.CONCERT_ID DESC;

Upvotes: 2

Hamidreza
Hamidreza

Reputation: 3128

You can use MIN or MAX in your WHERE clause to get your aim:

SELECT DISTINCT COURSE.COURSE_ID, COURSE.TITLE, 
COUNT(ATTENDANCE.STUDENT_ID) AS NUMBER_OF_STUDENTS
FROM ATTENDANCE, OFFERING, COURSE, STUDENT
WHERE OFFERING.OFFERING_ID = ATTENDANCE.OFFERING_ID
AND OFFERING.COURSE_ID = COURSE.COURSE_ID
AND STUDENT.STUDENT_ID = ATTENDANCE.STUDENT_ID
AND COURSE.COURSE_ID IN (SELECT MAX(t1.COURSE_ID) FROM COURSE t1 GROUP BY t1.TITLE)
GROUP BY COURSE.COURSE_ID, COURSE.TITLE
ORDER BY COURSE_ID DESC;

Upvotes: 0

Tamim Al Manaseer
Tamim Al Manaseer

Reputation: 3724

distinct means that the row as a whole should not be duplicated, in your case the type appears twice but with difference concert_id and number_of_customers.

Upvotes: 8

Related Questions