API tools faq
paste
Advertisement
yuliia_tech

Oracle_homework3_task1

yuliia_tech
Apr 14th, 2023
72
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Oracle 11 13.64 KB | None | 0 0
raw download clone embed print report
  1. SELECT CITY.COUNTRY VISITED_COUNTRY,
  2.        CITY.NAME CITY,
  3.        ACCOMMODATIONTYPE.NAME TYPE,
  4.        SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  5.        SUM(VISIT.PROFIT) AS PROFIT
  6. FROM VISIT
  7.        JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  8.        JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  9. GROUP BY
  10.     ROLLUP((CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME))
  11. ORDER BY
  12.     NVL(TO_CHAR(COUNTRY), 'Summary-total:'),
  13.     NVL(TO_CHAR(CITY), 'Summary-country:'),
  14.     NVL(TO_CHAR(TYPE), 'Summary-city:');
  15.  
  16. SELECT
  17.     CITY.COUNTRY VISITED_COUNTRY,
  18.     CITY.NAME CITY,
  19.     ACCOMMODATIONTYPE.NAME TYPE,
  20.     SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  21.     SUM(VISIT.PROFIT) AS PROFIT
  22. FROM
  23.     VISIT
  24.     JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  25.     JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  26. GROUP BY
  27.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  28. ORDER BY
  29.     VISITED_COUNTRY, CITY, TYPE
  30. CASE
  31.     WHEN TYPE IS NULL AND CITY IS NULL AND COUNTRY IS NULL THEN NVL(TO_CHAR(CITY.COUNTRY), 'Summary-total:')
  32. END;
  33.  
  34. SELECT
  35.     CITY.COUNTRY VISITED_COUNTRY,
  36.     CITY.NAME CITY,
  37.     ACCOMMODATIONTYPE.NAME TYPE,
  38.     SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  39.     SUM(VISIT.PROFIT) AS PROFIT
  40. FROM
  41.     VISIT
  42.     JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  43.     JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  44. GROUP BY
  45.     ROLLUP(NVL(TO_CHAR(CITY.COUNTRY), 'Summary-total:'),
  46.     NVL(TO_CHAR(CITY.NAME), 'Summary-country:'),
  47.     NVL(TO_CHAR(ACCOMMODATIONTYPE.NAME), 'Summary-city:'))
  48. ORDER BY
  49.     VISITED_COUNTRY, CITY, TYPE;
  50.  
  51.  
  52. SELECT NVL(TO_CHAR(CITY.COUNTRY), 'Summary-total:') VISITED_COUNTRY,
  53.        NVL(TO_CHAR(CITY.NAME), 'Summary-country:')  CITY,
  54.        NVL(TO_CHAR(ACCOMMODATIONTYPE.NAME), 'Summary-city:') TYPE,
  55.        SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  56.        SUM(VISIT.PROFIT) AS PROFIT
  57. FROM VISIT
  58.        JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  59.        JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  60. GROUP BY
  61.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  62. ORDER BY
  63.     VISITED_COUNTRY, CITY, TYPE;
  64.  
  65.  
  66. SELECT
  67.     CASE WHEN CITY.COUNTRY IS NULL THEN 'Summary-total:' ELSE CITY.COUNTRY END AS VISITED_COUNTRY,
  68.     CASE WHEN CITY.NAME IS NULL THEN 'Summary-country:' ELSE CITY.NAME END AS CITY,
  69.     CASE WHEN ACCOMMODATIONTYPE.NAME IS NULL THEN 'Summary-city:' ELSE ACCOMMODATIONTYPE.NAME END AS TYPE,
  70.     SUM(VISIT.NUMBER_OF_VISITORS) AS VISITORS,
  71.     SUM(VISIT.PROFIT) AS PROFIT
  72. FROM
  73.     VISIT
  74.     JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  75.     JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  76. GROUP BY
  77.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  78. ORDER BY
  79.     VISITED_COUNTRY, CITY, TYPE;
  80.  
  81.  
  82.  
  83.  
  84. SELECT
  85.     NVL(CITY.COUNTRY, 'Summary-total:') VISITED_COUNTRY,
  86.     NVL(CITY.NAME, 'Summary-country:') CITY,
  87.     NVL(ACCOMMODATIONTYPE.NAME, 'Summary-city:') TYPE,
  88.     SUM(NVL(VISIT.NUMBER_OF_VISITORS, 0)) VISITORS,
  89.     SUM(VISIT.PROFIT) AS PROFIT
  90. FROM
  91.     VISIT
  92.     RIGHT OUTER JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  93.     RIGHT OUTER JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  94. GROUP BY
  95.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  96. HAVING
  97.     GROUPING_ID(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME) < 3
  98. ORDER BY
  99.     NVL(CITY.COUNTRY, 'Summary-total:'),
  100.     CITY.NAME,
  101.     ACCOMMODATIONTYPE.NAME;
  102.    
  103.    
  104. SELECT COALESCE(CITY.COUNTRY, 'Summary-total:') AS VISITED_COUNTRY,
  105.        CASE WHEN CITY.COUNTRY IS NOT NULL
  106.        THEN COALESCE(CITY.NAME, 'Summary-country:') END AS CITY,
  107.        CASE WHEN CITY.COUNTRY IS NOT NULL AND CITY.NAME IS NOT NULL
  108.        THEN COALESCE(ACCOMMODATIONTYPE.NAME, 'Summary-city:') END AS TYPE,
  109.        SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  110.        SUM(VISIT.PROFIT) AS PROFIT
  111. FROM VISIT
  112.        JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  113.        JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  114. GROUP BY
  115.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  116. ORDER BY
  117.     VISITED_COUNTRY, CITY, TYPE;
  118.    
  119.    
  120. SELECT CITY.COUNTRY VISITED_COUNTRY,
  121.        CITY.NAME CITY,
  122.        ACCOMMODATIONTYPE.NAME TYPE,
  123.        SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  124.        SUM(VISIT.PROFIT) AS PROFIT
  125. FROM VISIT
  126.        JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  127.        JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  128. GROUP BY
  129.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  130. ORDER BY
  131.     VISITED_COUNTRY, CITY, TYPE;
  132.  
  133.  
  134. SELECT
  135.     DECODE(GROUPING(CITY.COUNTRY), NULL, 'Summary-total:', CITY.COUNTRY) VISITED_COUNTRY,
  136.     DECODE(GROUPING(CITY.NAME), NULL, 'Summary-country:', CITY.NAME) CITY,
  137.     DECODE(GROUPING(ACCOMMODATIONTYPE.NAME), NULL, 'Summary-city:', ACCOMMODATIONTYPE.NAME) TYPE,
  138.     SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  139.     SUM(VISIT.PROFIT) AS PROFIT
  140. FROM VISIT
  141.        JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  142.        JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  143. GROUP BY
  144.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  145. ORDER BY
  146.     VISITED_COUNTRY, CITY, TYPE;
  147.    
  148. SELECT
  149.     DECODE(GROUPING(CITY.COUNTRY), 0, 'Summary-total:', CITY.COUNTRY) VISITED_COUNTRY,
  150.     DECODE(GROUPING(CITY.NAME), 0, 'Summary-country:', CITY.NAME) CITY,
  151.     DECODE(GROUPING(ACCOMMODATIONTYPE.NAME), 0, 'Summary-city:', ACCOMMODATIONTYPE.NAME) TYPE,
  152.     SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  153.     SUM(VISIT.PROFIT) AS PROFIT
  154. FROM VISIT
  155.        JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  156.        JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  157. GROUP BY
  158.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  159. ORDER BY
  160.     VISITED_COUNTRY, CITY, TYPE;
  161.  
  162. SELECT CASE WHEN CITY.COUNTRY IS NULL
  163.        THEN 'Summary-total:' ELSE CITY.COUNTRY END AS VISITED_COUNTRY,
  164.        CASE WHEN CITY.COUNTRY IS NOT NULL AND CITY.NAME IS NULL
  165.        THEN 'Summary-country:' ELSE CITY.NAME END AS CITY,
  166.        CASE WHEN CITY.COUNTRY IS NOT NULL AND CITY.NAME IS NOT NULL AND ACCOMMODATIONTYPE.NAME IS NULL
  167.        THEN 'Summary-city:' ELSE ACCOMMODATIONTYPE.NAME END AS TYPE,
  168.        SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  169.        SUM(VISIT.PROFIT) AS PROFIT
  170. FROM VISIT
  171.        JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  172.        JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  173. GROUP BY
  174.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  175. ORDER BY
  176.     VISITED_COUNTRY, CITY, TYPE;  
  177.    
  178. SELECT
  179.     CASE WHEN GROUPING(CITY.COUNTRY) = 1
  180.          THEN 'Summary-total:'
  181.     ELSE CITY.COUNTRY
  182.     END AS VISITED_COUNTRY,
  183.     CASE WHEN GROUPING(CITY.COUNTRY) = 0
  184.               AND GROUPING(CITY.NAME) = 1
  185.          THEN 'Summary-country:'
  186.     ELSE CITY.NAME
  187.     END AS CITY,
  188.     CASE WHEN GROUPING(CITY.COUNTRY) = 0
  189.               AND GROUPING(CITY.NAME) = 0
  190.               AND GROUPING(ACCOMMODATIONTYPE.NAME) = 1
  191.          THEN 'Summary-city:'
  192.          ELSE ACCOMMODATIONTYPE.NAME
  193.     END AS TYPE,
  194.     SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  195.     SUM(VISIT.PROFIT) AS PROFIT
  196. FROM
  197.     VISIT
  198.     JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  199.     JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  200. GROUP BY
  201.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  202. ORDER BY
  203.     VISITED_COUNTRY, CITY, TYPE;
  204.  
  205.  
  206. SELECT
  207.   DECODE(GROUPING(CITY.COUNTRY), 1, 'Summary-total:', CITY.COUNTRY) AS VISITED_COUNTRY,
  208.   DECODE(GROUPING(CITY.NAME), 2, 'Summary-country:', CITY.NAME) AS CITY,
  209.   DECODE(GROUPING(ACCOMMODATIONTYPE.NAME), 3, 'Summary-city:', ACCOMMODATIONTYPE.NAME) AS TYPE,
  210.   SUM(VISIT.NUMBER_OF_VISITORS) AS VISITORS,
  211.   SUM(VISIT.PROFIT) AS PROFIT
  212. FROM
  213.   VISIT
  214.   JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  215.   JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  216. GROUP BY
  217.   GROUPING SETS(
  218.     (CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME),
  219.     (CITY.COUNTRY, CITY.NAME),
  220.     (CITY.COUNTRY),
  221.     ()
  222.   )
  223. ORDER BY
  224.   VISITED_COUNTRY, CITY, TYPE;
  225.  
  226. SELECT
  227.     NVL(TO_CHAR(CITY.COUNTRY), 'Summary-total:') AS VISITED_COUNTRY,
  228.     NVL(TO_CHAR(CITY.NAME), 'Summary-country:') AS CITY,
  229.     NVL(TO_CHAR(ACCOMMODATIONTYPE.NAME), 'Summary-city:') AS TYPE,
  230.     SUM(VISIT.NUMBER_OF_VISITORS) AS VISITORS,
  231.     SUM(VISIT.PROFIT) AS PROFIT
  232. FROM
  233.     VISIT
  234.     JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  235.     JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  236. GROUP BY GROUPING SETS (
  237.     (CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME),
  238.     (CITY.COUNTRY, CITY.NAME),
  239.     (CITY.COUNTRY),
  240.     ()
  241. )
  242. ORDER BY
  243.     VISITED_COUNTRY, CITY, TYPE;
  244.    
  245.  
  246. SELECT
  247.     CASE WHEN GROUPING_ID(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME) = 3
  248.        THEN 'Summary-total:' ELSE CITY.COUNTRY END AS VISITED_COUNTRY,
  249.     CASE WHEN GROUPING_ID(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME) = 2
  250.        THEN 'Summary-country:' ELSE CITY.NAME END AS CITY,
  251.     CASE WHEN GROUPING_ID(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME) = 1
  252.        THEN 'Summary-city:' ELSE ACCOMMODATIONTYPE.NAME END AS TYPE,
  253.     SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  254.     SUM(VISIT.PROFIT) AS PROFIT
  255. FROM
  256.     VISIT
  257.     JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  258.     JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  259. GROUP BY
  260.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  261. ORDER BY
  262.     VISITED_COUNTRY, CITY, TYPE;
  263.    
  264. SELECT
  265.   DECODE(GROUPING(CITY.COUNTRY), 1, 'Summary-total:', CITY.COUNTRY) AS VISITED_COUNTRY,
  266.   DECODE(GROUPING(CITY.COUNTRY, CITY.NAME),
  267.          3, 'Summary-city:',
  268.          2, 'Summary-country:',
  269.          CITY.NAME) AS CITY,
  270.   DECODE(GROUPING(ACCOMMODATIONTYPE.NAME), 1, 'Summary-city:', ACCOMMODATIONTYPE.NAME) AS TYPE,
  271.   SUM(VISIT.NUMBER_OF_VISITORS) AS VISITORS,
  272.   SUM(VISIT.PROFIT) AS PROFIT
  273. FROM
  274.   VISIT
  275.   JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  276.   JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  277. GROUP BY GROUPING SETS (
  278.   (CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME),
  279.   (CITY.COUNTRY, ACCOMMODATIONTYPE.NAME),
  280.   (CITY.COUNTRY),
  281.   ()
  282. )
  283. ORDER BY VISITED_COUNTRY, CITY, TYPE;
  284.  
  285. SELECT
  286.   DECODE(GROUPING(CITY.COUNTRY), 4, 'Summary-total:', CITY.COUNTRY) AS VISITED_COUNTRY,
  287.   DECODE(GROUPING(CITY.NAME), 2, 'Summary-country:', CITY.NAME) AS CITY,
  288.   DECODE(GROUPING(ACCOMMODATIONTYPE.NAME), 3, 'Summary-city:', ACCOMMODATIONTYPE.NAME) AS TYPE,
  289.   SUM(VISIT.NUMBER_OF_VISITORS) AS VISITORS,
  290.   SUM(VISIT.PROFIT) AS PROFIT
  291. FROM
  292.   VISIT
  293.   JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  294.   JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  295. GROUP BY
  296.   ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  297. ORDER BY
  298.   VISITED_COUNTRY, CITY, TYPE;
  299.  
  300. SELECT DECODE(GROUPING(CITY.COUNTRY), 1, 'Summary-total:', CITY.COUNTRY) AS VISITED_COUNTRY,
  301.        DECODE(GROUPING_ID(CITY.COUNTRY, CITY.NAME),
  302.               3, 'Summary-city:',
  303.               2, 'Summary-country:',
  304.               CITY.NAME) AS CITY,
  305.        DECODE(GROUPING(ACCOMMODATIONTYPE.NAME), 1, 'Summary-city:', ACCOMMODATIONTYPE.NAME) AS TYPE,
  306.        SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  307.        SUM(VISIT.PROFIT) AS PROFIT
  308. FROM VISIT
  309.        JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  310.        JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  311. GROUP BY ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  312. ORDER BY VISITED_COUNTRY, CITY, TYPE;
  313.  
  314.  
  315. SELECT
  316.     CASE WHEN GROUPING_ID() = 4
  317.        THEN 'Summary-total:' ELSE CITY.COUNTRY END AS VISITED_COUNTRY,
  318.     CASE WHEN GROUPING_ID(CITY.COUNTRY, CITY.NAME) = 2
  319.        THEN 'Summary-country:' ELSE CITY.NAME END AS CITY,
  320.     CASE WHEN GROUPING_ID(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME) = 1
  321.        THEN 'Summary-city:' ELSE ACCOMMODATIONTYPE.NAME END AS TYPE,
  322.     SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  323.     SUM(VISIT.PROFIT) AS PROFIT
  324. FROM
  325.     VISIT
  326.     JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  327.     JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  328. GROUP BY
  329.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  330. ORDER BY
  331.     VISITED_COUNTRY, CITY, TYPE;
  332.  
  333.  
  334. SELECT
  335.     CASE WHEN GROUPING(CITY.COUNTRY) = 1
  336.        THEN 'Summary-total:' ELSE CITY.COUNTRY END AS VISITED_COUNTRY,
  337.     CASE WHEN GROUPING(CITY.COUNTRY) = 0 AND GROUPING(CITY.NAME) = 1
  338.        THEN 'Summary-country:' ELSE CITY.NAME END AS CITY,
  339.     CASE WHEN GROUPING(CITY.COUNTRY) = 0 AND GROUPING(CITY.NAME) = 0 AND GROUPING(ACCOMMODATIONTYPE.NAME) = 1
  340.        THEN 'Summary-city:' ELSE ACCOMMODATIONTYPE.NAME END AS TYPE,
  341.     SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
  342.     SUM(VISIT.PROFIT) AS PROFIT
  343. FROM
  344.     VISIT
  345.     JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  346.     JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  347. GROUP BY
  348.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  349. ORDER BY
  350.     VISITED_COUNTRY, CITY, TYPE;
  351.  
  352.  
  353. SELECT
  354.     DECODE(GROUPING(CITY.COUNTRY), 1, 'Summary-total:', CITY.COUNTRY) AS VISITED_COUNTRY,
  355.     DECODE(GROUPING(CITY.COUNTRY), 0,
  356.            DECODE(GROUPING(CITY.NAME), 1, 'Summary-country:', CITY.NAME),
  357.            NULL) AS CITY,
  358.     DECODE(GROUPING(CITY.COUNTRY), 0,
  359.            DECODE(GROUPING(CITY.NAME), 0,
  360.                   DECODE(GROUPING(ACCOMMODATIONTYPE.NAME), 1, 'Summary-city:', ACCOMMODATIONTYPE.NAME),
  361.                   NULL),
  362.            NULL) AS TYPE,
  363.     SUM(VISIT.NUMBER_OF_VISITORS) AS VISITORS,
  364.     SUM(VISIT.PROFIT) AS PROFIT
  365. FROM
  366.     VISIT
  367.     JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
  368.     JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
  369. GROUP BY
  370.     ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
  371. ORDER BY
  372.     VISITED_COUNTRY, CITY, TYPE;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!