0

I am doing an assignment and I am close to completing it. However the package balance appears on multiple lines and I would like to combine the balances for each package type.

IF OBJECT_ID('vwPackageRevenue') IS NOT NULL
    DROP VIEW vwPackageRevenue
GO

CREATE VIEW vwPackageRevenue
AS
SELECT (dbo.packageWH.PNAME) AS 'Package Name'
    ,(COSTPERSON * COUNT(GFirstName)) AS 'Total Revenue'
FROM dbo.packageWH
JOIN dbo.reservationWH ON dbo.packageWH.P_ID = dbo.reservationWH.P_ID
JOIN guestWH ON dbo.reservationWH.RNUM = dbo.guestWH.RNUM
GROUP BY dbo.packageWH.COSTPERSON
    ,dbo.packageWH.PNAME
    ,dbo.guestWH.GFIRSTNAME 
GO

SELECT *
FROM vwPackageRevenue

Any ideas how to complete this?

1 Answer 1

0

I think you just need to add a SUM and I am not sure what the > signs are doing in there.

IF OBJECT_ID('vwPackageRevenue') IS NOT NULL
    DROP VIEW vwPackageRevenue
GO

CREATE VIEW vwPackageRevenue
AS
SELECT (dbo.packageWH.PNAME) AS 'Package Name'
    ,SUM((COSTPERSON * COUNT(GFirstName)))  AS 'Total Revenue'
FROM dbo.packageWH
JOIN dbo.reservationWH ON  dbo.packageWH.P_ID = dbo.reservationWH.P_ID
JOIN guestWH ON dbo.reservationWH.RNUM = dbo.guestWH.RNUM
GROUP BY 
dbo.packageWH.PNAME

GO

SELECT *
FROM vwPackageRevenue

Does this work for you?

3
  • might need to tweak the group by clause as well, s/b just PNAME, I think. May 1, 2020 at 13:02
  • Thanks @JonathanFite, that's probably right.
    – BCM
    May 1, 2020 at 14:34
  • Thanks for your response. When trying this I got errors because it didn't like a COUNT function being within SUM. Also it would throw an error if I only had dbo.packageWH.PNAME within the GROUP BY clause. HOWEVER I have figured out the solution was simply removing dbo.guestWH.GFIRSTNAME from the GROUP BY clause from the previous code. May 2, 2020 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.