Geek asks and answers

In SQL, how to aggregate in a field inside a table

I'm sorry TITLE is not so specific. I will try to explain: I am new to SQL. I work and wrote a query that contains 9 columns that extract information from many tables. on the 9th column the names of the types of machines are indicated, and on the third - a value representing the time during which the machine worked for a month. I need to add the 10th column, which will have for each type of machine, the maximum of the 3rd column for this type. let's say there are 5 XR machines (5 rows in the table) with time (3rd column) 1,2,3,4,5 (in hours). I need that in the 10th column, all the rows where the type of machine is XR will be 5, since this is the maximum for this type of machine.

How should I do it?

Any help would be greatly appreciated!

+3
source share
1 answer

In SQL Server, Oracleand PostgreSQL:

SELECT  *, MAX(col3) OVER (PARTITION BY col9)
FROM    mytable

In MySQL:

SELECT  mt.*, maxcol3
FROM    (
        SELECT  col9, MAX(col3) AS maxcol3
        FROM    mytable
        ) q
JOIN    mytable mt
ON      mt.col9 = q.col9
+6
source

More articles:


All Articles