3
I need to develop a screen that shows the TOP 50 most accessed menus. Tell the number of accesses and which city it belongs to. Sort by number of accesses and select values for the last 30 days.
To solve the first situation I have this command:
SELECT
menu. NAME AS Nome,
city. NAME AS Cidade,
count(*) AS qtdAcessos
FROM
history.view_menu
JOIN menu ON menu.menu_id = view_menu.menu_id
JOIN address ON address.address_id = menu.address_id
JOIN city ON city.city_id = address.city_id
GROUP BY
menu. NAME,
city. NAME
ORDER BY
qtdAcessos DESC
LIMIT 50
And the following command is to return the values for the last 30 days:
SELECT
view_menu. CURRENT_DATE
FROM
history.view_menu
WHERE
view_menu. CURRENT_DATE BETWEEN CURRENT_DATE - 30
AND CURRENT_DATE
My question is: how to join these two commands?
Post also the structure of the tables, please.
– Kenny Rafael
history_menu(view_manu_id current_date menu_id people_id)
– Carla Vieira
In a quick look your Where of the last query is wrong. WHERE view_menu. CURRENT_DATE BETWEEN CURRENT_DATE - 30 AND CURRRENT_DATE
– Marco Souza
Something else would not only suffice for this Where in the first query?
– Marco Souza