Here even though the screening departhub respectively and desthub repetitive data, but after screening departhub and desthub still have duplicate data, the great god this what to do,

Other: departhub and desthub is on the table in the journey,

Calculate the total want to list the biggest value, I in front of the count function and Max complains "ORA - 00937: not a single set of group function".

CodePudding user response:

Stupid way is
Select the name, the MAX (TOTAL) FROM (your own SQL) group by name

CodePudding user response:

I understand I don't know if you have any questions
After departhub distinct data is 123 after the count is 3
After desthub distinct data is 345 after the count is 3
After the total is 6
You want a distinct after both columns is 12345 total is 5
If you understand no problem SQL below
Select traveller. Givenname | | "| | traveller. Familyname as the name,
Traveller. Travellerid, count (distinct j. epartAll) as the total
From the traveller, (
The select journeyid travellerid, departhub as departAll from journey
Union all
The select journeyid travellerid, desthub from journey
J, reservation
Where j.j ourneyid=reservation. Journeyid and traveller. Travellerid=reservation. Travellerid
Group by traveller. Givenname | | "| | traveller. Familyname, traveller. Travellerid;


The select Max (total) from (
The above SQL
);

CodePudding user response:

Your demand is clear, it is better for analog data and expected results