Question 20111 in Mysql Question posted in
The official MySQL documentation can be found here.

Correcting SQL using SET in MySQL to display

ITrying to get 2 separate select statements to show in one result set using a user defined variable. Should I use a Union or With somewhere here?

SET @X1 = 
((SELECT SUM(ef.Fee * (rd.ActualReturn - ra.startdate)) as "total revenue from rentals"
FROM EqRentDetails as rd
JOIN EquipmentInventory  as ei ON rd.ItemSKU = ei.ItemSKU
JOIN EqRentAgreement as ra ON rd.RentalID = ra.RentalID
JOIN customers ON customers.CustomerID = ra.CustomerID
JOIN EquipmentFee as ef ON ef.EquipmentID = ei.EquipmentID and customers.CategoryKey = 
ef.Customercategory
WHERE rd.Actualreturn IS NOT NULL
GROUP BY ra.CustomerID));


SET @Y =
(SELECT SignUp.ParticipantID, SUM(avt.TripFee) as "Total revenue from trip fees"
FROM SignUp
JOIN Offerings as o on o.OfferingCode = SignUp.OfferingCode
JOIN AvailableTrips as avt on avt.TripID = o.TripCode
JOIN Customers on Customers.CustomerID = SignUp.ParticipantID
group by SignUp.ParticipantID);

#for display

Select @Y, @X;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    Create View Customer_total_rental_rev AS
((SELECT customers.CustomerID, SUM(ef.Fee * (rd.ActualReturn - ra.startdate)) as 
"total revenue from rentals"
FROM EqRentDetails as rd
JOIN EquipmentInventory  as ei ON rd.ItemSKU = ei.ItemSKU
JOIN EqRentAgreement as ra ON rd.RentalID = ra.RentalID
JOIN customers ON customers.CustomerID = ra.CustomerID
JOIN EquipmentFee as ef ON ef.EquipmentID = ei.EquipmentID and 
customers.CategoryKey = ef.Customercategory
WHERE rd.Actualreturn IS NOT NULL
GROUP BY ra.CustomerID)) ;

#UNION;

CREATE View Participant_Trip_Rev AS
(SELECT SignUp.ParticipantID, SUM(avt.TripFee) as "Total revenue from trip fees"
FROM SignUp
JOIN Offerings as o on o.OfferingCode = SignUp.OfferingCode
JOIN AvailableTrips as avt on avt.TripID = o.TripCode
JOIN Customers on Customers.CustomerID = SignUp.ParticipantID
group by SignUp.ParticipantID);

Select Customer_total_rental_rev.*, Participant_Trip_Rev.*
 from Customer_total_rental_rev, Participant_Trip_Rev;

  2. 0

    Well you can just add the two queries in a single Select statement. This way you can reduce some extra SQL code execution. Most of the case it does not matter but some cases it may work as optimization. Here is a sample

    SELECT
  (SELECT SUM(ef.Fee * (rd.ActualReturn - ra.startdate)) as "total revenue from rentals"
   FROM EqRentDetails as rd
   JOIN EquipmentInventory as ei ON rd.ItemSKU = ei.ItemSKU
   JOIN EqRentAgreement as ra ON rd.RentalID = ra.RentalID
   JOIN customers ON customers.CustomerID = ra.CustomerID
   JOIN EquipmentFee as ef ON ef.EquipmentID = ei.EquipmentID and customers.CategoryKey = ef.Customercategory
   WHERE rd.Actualreturn IS NOT NULL
   GROUP BY ra.CustomerID) AS 'total revenue from rentals',
  (SELECT SUM(avt.TripFee) as "Total revenue from trip fees"
   FROM SignUp
   JOIN Offerings as o ON o.OfferingCode = SignUp.OfferingCode
   JOIN AvailableTrips as avt ON avt.TripID = o.TripCode
   JOIN Customers ON Customers.CustomerID = SignUp.ParticipantID
   GROUP BY SignUp.ParticipantID) AS 'Total revenue from trip fees';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search