CODEWITHSUNDEEP
sqloracle-database
Prime
Prime

Reputation: 140

ORACLE How to get data from a table depending on year and the amount of duplicates

so the question is "Produce a list of those employees who have made bookings at the Sports Club more than 5 times in the last calendar year (this should be calculated and not hard coded). Order these by the number of bookings made."

what i am struggling with is being able to get todays year and then subtract 1 year aswell as showing ONLY one name of people appearing 5 times. this is what i have so far, but it doesnt work.

SELECT DISTINCT 
FIRSTNAME,SURNAME,DATEBOOKED,MEMBERSHIPTYPEID,BOOKINGID,MEMBER.MEMBERID
FROM MEMBERSHIP,MEMBER,BOOKING
WHERE MEMBER.MEMBERID = BOOKING.MEMBERID
AND MEMBERSHIP.MEMBERSHIPTYPEID = 3 AND BOOKING.DATEBOOKED = (SYSDATE, 
'DD/MON/YY -1') AND FIRSTNAME IN (
SELECT FIRSTNAME
FROM MEMBER
GROUP BY FIRSTNAME,SURNAME
HAVING COUNT(FIRSTNAME) >= 5
)
ORDER BY MEMBER.MEMBERID;

EDR

tabele structures

Upvotes: 0

Views: 73

Answers (3)

APC
APC

Reputation: 146239

What you need is this day last year. There are various different ways of calculating that. For instance, 

add_months(sysdate, -12)

or 

sysdate - interval '1' year

The subquery looks a bit whacky too. What you're after is the number of BOOKING records in the last year. So the subquery should drive off the BOOKING table, and the date filter should be there.

Finally, there is a missing join condition between MEMBER and MEMBERSHIP. That's probably why you think you need that distinct; fix the join and you'll get the result set you want and not a product. One advantage of the ANSI 92 explicit join syntax is that it stops us from missing joins.

Your query needs to be sorted by the number of bookings, so you need to count those and sort by the total. This means you don't actually need a subquery at all.

So your query should look something like:

SELECT MEMBER.MEMBERID
        , MEMBER.FIRSTNAME
        , MEMBER.SURNAME
        , count(BOOKING.BOOKID) as no_of_bookings
FROM MEMBER 
     inner join MEMBERSHIP
     on MEMBER.MEMBERID = MEMBERSHIP.MEMBERID
     inner join BOOKING
     on MEMBER.MEMBERID = BOOKING.MEMBERID 
WHERE MEMBERSHIP.MEMBERSHIPTYPEID = 3
and BOOKING.DATEBOOKED >= add_months(trunc(sysdate), -12) 
GROUP BY MEMBER.MEMBERID
        , MEMBER.FIRSTNAME
        , MEMBER.SURNAME
    HAVING COUNT(*) >= 5
ORDER BY no_of_bookings desc;

Here is a SQL Fiddle demo for my query.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269923

I think this answers the question:

SELECT m.FIRSTNAME, m.SURNAME, m.MEMBERID
FROM MEMBER m JOIN
     BOOKING b
     ON m.MEMBERID = b.MEMBERID JOIN
     MEMBERSHIP ms
     ON ms.MEMBERID = m.MEMBERID
WHERE ms.MEMBERSHIPTYPEID = 3 AND
      B.DATEBOOKED >= SYSDATE - INTERVAL '1 YEAR'
GROUP BY  m.FIRSTNAME, m.SURNAME, m.MEMBERID
HAVING COUNT(*) >= 5
ORDER BY COUNT(* DESC;

Table aliases make the query much easier to write and to read.

Upvotes: 0

Kedar Limaye
Kedar Limaye

Reputation: 1041

SELECT 
FIRSTNAME,SURNAME,DATEBOOKED,MEMBERSHIPTYPEID,BOOKINGID,MEMBER.MEMBERID
FROM MEMBERSHIP,MEMBER,BOOKING
WHERE MEMBER.MEMBERID = BOOKING.MEMBERID
AND MEMBERSHIP.MEMBERSHIPTYPEID = 3 AND BOOKING.DATEBOOKED between (SYSDATE) and  
(sysdate - interval '1' year) 
AND FIRSTNAME IN (
SELECT distinct FIRSTNAME
FROM MEMBER
GROUP BY FIRSTNAME,SURNAME
HAVING COUNT(FIRSTNAME) >= 5
)
ORDER BY MEMBER.MEMBERID;

Upvotes: 0

Related Questions