SQL SELECT for max and min values with virtual columns

Get Help SQL
,
1

Hi, i need help to create a query to:

I have this table

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

Thanks a lot,

2

This post was flagged by the community and is temporarily hidden.

3

Hi,

First of all Thanks.

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

Thanks again,

4

Try this and let me know how it goes :slight_smile:

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.
5

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,

6

Based on the table structure and requirements you provided, the query I suggested earlier should give you the desired output. However, if you are having issues when joining the results with other tables, there may be some additional factors at play that could be causing the problem.

One possibility is that the join condition between the tables is not specified correctly, which can result in the wrong rows being joined together. Another possibility is that the data types of the columns being joined are not compatible, which can cause errors or unexpected results.

If you can give me more information about the structure of the other tables you’re trying to join with, and the join condition you’re using, I can help you further diagnose the issue and suggest potential solutions.

1 Like
7

Hi,

Just works ok. There was an error in my conditions in the join.