Question: How to put DISTINCT GuestCode inside this sql statement? If distinct G

Question

Question:

How to put DISTINCT GuestCode inside this sql statement?
If distinct GuestCode i find 90, without distinct get 150 so i want sum 90 in one day
My database have same Guest Code so i want sum distinct one, but group by 365 day.

Correct my query and explain where is my false.

SELECT
       (SELECT SUM(SUBSTR(`AverageRoomRate inRangeExclusive`, 3, 10))) As ExRate,
       (SELECT SUM(SUBSTR(`AverageRoomRate inRangeInclusive`, 3, 10))) As InRate
   FROM CMDapr
       WHERE YEAR(STR_TO_DATE(`ArrivalDate`,'%d-%M-%YY')) = '2013' GROUP BY STR_TO_DATE(`ArrivalDate`,'%d-%M-%YY')

Explanation / Answer

How to put DISTINCT GuestCode inside this sql statement?
If distinct GuestCode i find 90, without distinct get 150 so i want sum 90 in one day
My database have same Guest Code so i want sum distinct one, but group by 365 day.

Correct my query and explain where is my false.

SELECT
(SELECT SUM(SUBSTR(`AverageRoomRate inRangeExclusive`, 3, 10))) As ExRate,
(SELECT SUM(SUBSTR(`AverageRoomRate inRangeInclusive`, 3, 10))) As InRate
FROM CMDapr
WHERE YEAR(STR_TO_DATE(`ArrivalDate`,'%d-%M-%YY')) = '2013' GROUP BY STR_TO_DATE(`ArrivalDate`,'%d-%M-%YY')

Help :

The SQL HAVING Clause is used in combination with the GROUP BY Clause to restrict the groups of returned rows to only those whose the condition is TRUE. So when you have any aggregate function like GROUP BY you should use HAVING.

Consider this for better understand having:

A GROUP BY s a query that takes a table and summarizes it into another table. You summarize the original table by grouping the original table into subsets (based upon the attributes that you specify in the group by).

The HAVING is simply an equivalent to a WHERE clause after the group by has executed and before the SELECT PART is computed.

So I would suggest you to try this

SQL Code:

SELECT
something...
FROM
some table
WHERE
some condition
GROUP BY
something
HAVING DISTINCT some_condition

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.