name year number value
NAME1 2021 001406 189
NAME2 2022 001406 150
NAME3 2022 001406 800
NAME2 2020 001592 60
NAME4 2021 001592 89,25
NAME1 2022 001592 250
NAME2 2022 001592 50
I need that when a number appears more than one time in the same year, it shows the highest value in a column called MAXVALUE and a “virtual” column with the min value of the repeated rows, called MINVALUE. For example, in the table, VALUE 001592 appears three times; 2 for year 2022 and 1 for year 2020. The result must seen as:
year number maxvalue minvalue
2022 001592 250 50
2021 001592 89,25 0
This works ok, but i need when only there’s 1 row, minvalue must be 0. If there are two rows with the same number in same year, max value must be the highest and minvalue the minimum… Butg when there’s only 1 row and by this only are one value, max value must be this unic value and in minvalue must appear as 0
SELECT year, number, MAX(value) AS maxvalue,
CASE
WHEN COUNT(*) > 1 THEN MIN(value)
ELSE 0
END AS minvalue
FROM table_name
GROUP BY year, number
HAVING COUNT(*) > 0;
here’s what i changed:
Use a CASE statement to set minvalue to 0 if there’s only one row for a group.
Keep the MAX() function to get the maximum value for each group.
Use the GROUP BY clause to group the data by year and number.
Use the HAVING clause to exclude groups with a count of 0.
It works like a charm. The only thing that when i try to join it with other tables, is giving me wrong results. I am going to see if I can put the structure of the tables that I use and thus be able to show what I am looking for. Many many thanks,
