SQL Zoo Guest House Gross income by week. How can i show zero values when they are omitted? [on hold]
up vote
-2
down vote
favorite
SELECT
DATE_ADD(MAKEDATE(2016, 7), INTERVAL WEEK(DATE_ADD(booking.booking_date, INTERVAL booking.nights - 5 DAY), 0) WEEK) AS Thursday,
SUM(booking.nights * rate.amount) + SUM(e.amount) as weekly_income
FROM
booking
JOIN
rate
ON (booking.occupants = rate.occupancy
AND booking.room_type_requested = rate.room_type)
LEFT JOIN
(
SELECT
booking_id,
SUM(amount) as amount
FROM
extra
group by
booking_id
)
AS e
ON (e.booking_id = booking.booking_id)
GROUP BY
Thursday;
I have tried to do the question with Right Join and Left Join and COALESCE(), but nothing seems to work. It gives me every result except the ones with zero values. The result I get is this :
Result:
Thursday weekly_income
2016-11-10 12608.94
2016-11-17 13552.56
2016-11-24 12929.69
2016-12-01 11685.14
2016-12-08 13093.79
2016-12-15 8975.87
2016-12-22 1395.77
and the result should be:
+------------+---------------+
| Thursday | weekly_income |
+------------+---------------+
| 2016-11-03 | 0.00 |
| 2016-11-10 | 12608.94 |
| 2016-11-17 | 13552.56 |
| 2016-11-24 | 12929.69 |
| 2016-12-01 | 11685.14 |
| 2016-12-08 | 13093.79 |
| 2016-12-15 | 8975.87 |
| 2016-12-22 | 1395.77 |
| 2016-12-29 | 0.00 |
| 2017-01-05 | 0.00 |
+------------+---------------+
The diagram of relationships can be found here: http://sqlzoo.net/wiki/Guest_House And you can try the query in http://sqlzoo.net/wiki/Guest_House_Assessment_Hard as Question 15 Thank you for the help in advance.
sql mysql
asked 11 hours ago
Midnight
11
New contributor
Midnight is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as off-topic by πάντα ῥεῖ, Sᴀᴍ Onᴇᴌᴀ, 200_success, Jamal♦ 4 hours ago
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – πάντα ῥεῖ, Sᴀᴍ Onᴇᴌᴀ, 200_success, Jamal
If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
-2
down vote
favorite
SELECT
DATE_ADD(MAKEDATE(2016, 7), INTERVAL WEEK(DATE_ADD(booking.booking_date, INTERVAL booking.nights - 5 DAY), 0) WEEK) AS Thursday,
SUM(booking.nights * rate.amount) + SUM(e.amount) as weekly_income
FROM
booking
JOIN
rate
ON (booking.occupants = rate.occupancy
AND booking.room_type_requested = rate.room_type)
LEFT JOIN
(
SELECT
booking_id,
SUM(amount) as amount
FROM
extra
group by
booking_id
)
AS e
ON (e.booking_id = booking.booking_id)
GROUP BY
Thursday;
I have tried to do the question with Right Join and Left Join and COALESCE(), but nothing seems to work. It gives me every result except the ones with zero values. The result I get is this :
Result:
Thursday weekly_income
2016-11-10 12608.94
2016-11-17 13552.56
2016-11-24 12929.69
2016-12-01 11685.14
2016-12-08 13093.79
2016-12-15 8975.87
2016-12-22 1395.77
and the result should be:
+------------+---------------+
| Thursday | weekly_income |
+------------+---------------+
| 2016-11-03 | 0.00 |
| 2016-11-10 | 12608.94 |
| 2016-11-17 | 13552.56 |
| 2016-11-24 | 12929.69 |
| 2016-12-01 | 11685.14 |
| 2016-12-08 | 13093.79 |
| 2016-12-15 | 8975.87 |
| 2016-12-22 | 1395.77 |
| 2016-12-29 | 0.00 |
| 2017-01-05 | 0.00 |
+------------+---------------+
The diagram of relationships can be found here: http://sqlzoo.net/wiki/Guest_House And you can try the query in http://sqlzoo.net/wiki/Guest_House_Assessment_Hard as Question 15 Thank you for the help in advance.
sql mysql
asked 11 hours ago
Midnight
11
New contributor
Midnight is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as off-topic by πάντα ῥεῖ, Sᴀᴍ Onᴇᴌᴀ, 200_success, Jamal♦ 4 hours ago
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – πάντα ῥεῖ, Sᴀᴍ Onᴇᴌᴀ, 200_success, Jamal
If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
-2
down vote
favorite
up vote
-2
down vote
favorite
SELECT
DATE_ADD(MAKEDATE(2016, 7), INTERVAL WEEK(DATE_ADD(booking.booking_date, INTERVAL booking.nights - 5 DAY), 0) WEEK) AS Thursday,
SUM(booking.nights * rate.amount) + SUM(e.amount) as weekly_income
FROM
booking
JOIN
rate
ON (booking.occupants = rate.occupancy
AND booking.room_type_requested = rate.room_type)
LEFT JOIN
(
SELECT
booking_id,
SUM(amount) as amount
FROM
extra
group by
booking_id
)
AS e
ON (e.booking_id = booking.booking_id)
GROUP BY
Thursday;
I have tried to do the question with Right Join and Left Join and COALESCE(), but nothing seems to work. It gives me every result except the ones with zero values. The result I get is this :
Result:
Thursday weekly_income
2016-11-10 12608.94
2016-11-17 13552.56
2016-11-24 12929.69
2016-12-01 11685.14
2016-12-08 13093.79
2016-12-15 8975.87
2016-12-22 1395.77
and the result should be:
+------------+---------------+
| Thursday | weekly_income |
+------------+---------------+
| 2016-11-03 | 0.00 |
| 2016-11-10 | 12608.94 |
| 2016-11-17 | 13552.56 |
| 2016-11-24 | 12929.69 |
| 2016-12-01 | 11685.14 |
| 2016-12-08 | 13093.79 |
| 2016-12-15 | 8975.87 |
| 2016-12-22 | 1395.77 |
| 2016-12-29 | 0.00 |
| 2017-01-05 | 0.00 |
+------------+---------------+
The diagram of relationships can be found here: http://sqlzoo.net/wiki/Guest_House And you can try the query in http://sqlzoo.net/wiki/Guest_House_Assessment_Hard as Question 15 Thank you for the help in advance.
sql mysql
asked 11 hours ago
Midnight
11
New contributor
Midnight is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
SELECT
DATE_ADD(MAKEDATE(2016, 7), INTERVAL WEEK(DATE_ADD(booking.booking_date, INTERVAL booking.nights - 5 DAY), 0) WEEK) AS Thursday,
SUM(booking.nights * rate.amount) + SUM(e.amount) as weekly_income
FROM
booking
JOIN
rate
ON (booking.occupants = rate.occupancy
AND booking.room_type_requested = rate.room_type)
LEFT JOIN
(
SELECT
booking_id,
SUM(amount) as amount
FROM
extra
group by
booking_id
)
AS e
ON (e.booking_id = booking.booking_id)
GROUP BY
Thursday;
I have tried to do the question with Right Join and Left Join and COALESCE(), but nothing seems to work. It gives me every result except the ones with zero values. The result I get is this :
Result:
Thursday weekly_income
2016-11-10 12608.94
2016-11-17 13552.56
2016-11-24 12929.69
2016-12-01 11685.14
2016-12-08 13093.79
2016-12-15 8975.87
2016-12-22 1395.77
and the result should be:
+------------+---------------+
| Thursday | weekly_income |
+------------+---------------+
| 2016-11-03 | 0.00 |
| 2016-11-10 | 12608.94 |
| 2016-11-17 | 13552.56 |
| 2016-11-24 | 12929.69 |
| 2016-12-01 | 11685.14 |
| 2016-12-08 | 13093.79 |
| 2016-12-15 | 8975.87 |
| 2016-12-22 | 1395.77 |
| 2016-12-29 | 0.00 |
| 2017-01-05 | 0.00 |
+------------+---------------+
The diagram of relationships can be found here: http://sqlzoo.net/wiki/Guest_House And you can try the query in http://sqlzoo.net/wiki/Guest_House_Assessment_Hard as Question 15 Thank you for the help in advance.
sql mysql
sql mysql
asked 11 hours ago
Midnight
11
New contributor
Midnight is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 11 hours ago
Midnight
11
New contributor
Midnight is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 11 hours ago
Midnight
11
New contributor
Midnight is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 11 hours ago
Midnight
11
asked 11 hours ago
Midnight
11
11
New contributor
Midnight is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Midnight is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Midnight is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as off-topic by πάντα ῥεῖ, Sᴀᴍ Onᴇᴌᴀ, 200_success, Jamal♦ 4 hours ago
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – πάντα ῥεῖ, Sᴀᴍ Onᴇᴌᴀ, 200_success, Jamal
If this question can be reworded to fit the rules in the help center, please edit the question.
put on hold as off-topic by πάντα ῥεῖ, Sᴀᴍ Onᴇᴌᴀ, 200_success, Jamal♦ 4 hours ago
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – πάντα ῥεῖ, Sᴀᴍ Onᴇᴌᴀ, 200_success, Jamal
If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
