I am sure this is a simple fix, but can not figure it out. I have a COUNT function that needs to count the total number of Rentals. Alone, the function works just fine. I have tried using DISTINCT, tweaking the GROUP BY, and ORDER BY, but to not avail.

When I add the DATEDIFF function to get the NumberofDays rented, the COUNT function does not work the way as intended. I should be getting some results with "2" instead of "1". Note that I need to order by the Rentals.Boat_ID. Here is my code and table results. Thanks

SELECT
    BOATS.Boat_Brand,
    COUNT(RENTALS.Boat_ID) AS NumberofRentals,
    DATEDIFF(Day, RENTALS.Rental_StartDay, RENTALS.Rental_EndDay) 1) as NumberofDaysRented
FROM RENTALS
INNER JOIN BOATS
    ON RENTALS.Boat_ID = BOATS.Boat_ID 
GROUP BY
    BOATS.Boat_Brand,
    RENTALS.Rental_StartDay,
    RENTALS.Rental_EndDay
ORDER BY
    COUNT(RENTALS.Boat_ID) DESC;

EXPECTED TABLE/RESULTS:

CodePudding user response：

seems like you need to group only by brand and get sum of rented days :

SELECT
    BOATS.Boat_Brand,
    COUNT(RENTALS.Boat_ID) AS NumberofRentals,
    SUM(DATEDIFF(Day,RENTALS.Rental_StartDay,RENTALS.Rental_EndDay)   1)) as NumberofDaysRented
FROM
    RENTALS
    INNER JOIN BOATS ON RENTALS.Boat_ID = BOATS.Boat_ID
GROUP BY
    BOATS.Boat_Brand
ORDER BY
    COUNT(RENTALS.Boat_ID) DESC;