CODEWITHSUNDEEP
sqlpostgresqlselectmin
i_raqz
i_raqz

Reputation: 2959

SQL min function

I am trying to get two columns, frequency and frequency - min(frequency) but all I see is zero for the second column. What could possibly be wrong?

SELECT
    frequency, frequency - min(frequency)
    FROM
    words
GROUP BY id
ORDER BY frequency;

enter image description here

Upvotes: 2

Views: 86

Answers (4)

jarlh
jarlh

Reputation: 44746

Add a sub-query that returns the min frequency value:

SELECT
    frequency, frequency - (select min(frequency) from words)
    FROM
    words
ORDER BY frequency;

Edit:

Wrap it up in a derived table:

SELECT frequency, frequency - minfreq, frequency + minfreq
FROM words
    CROSS JOIN (select min(frequency) minfreq from words) dt
ORDER BY frequency

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

This is certainly a queer query. You group by ID, so you get one result record per ID. But ID suggests that this is the table's ID identifying records uniquely. So GROUP BY id doesn't change anything, you still get all records in your results. With one exception: min(frequency) now means the minimum frequency per group. As the "group" is one record, the minimum value is the the value itself of course. The non-aggregated frequency is also the records' frequency uniquely identified by ID. So your query can be re-written as:

SELECT
  frequency, frequency - frequency
FROM words
ORDER BY frequency;

I suppose you want to compare each record's frequency with the minimum frequency found in the table? You'd get this value in a subquery:

SELECT
  frequency, frequency - (select min(frequency) from words)
FROM words
ORDER BY frequency;

Or:

SELECT
  w.frequency, w.frequency - m.min_frequncy
FROM words w
CROSS JOIN (select min(frequency) as min_frequncy from words) m
ORDER BY frequency;

Upvotes: 0

Obaidul
Obaidul

Reputation: 57

Try it:

SELECT frequency, frequency-min_frequency
FROM ( 
SELECT frequency,  MIN(frequency) AS min_frequency 
FROM words
GROUP BY frequency
) as A 
ORDER BY frequency;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311143

Your query groups by the unique value of frequency. In each such group, the minimal frequency is just the frequency itself, so you always get 0 when subtracting the two. Instead, you could use the windowing version of min:

SELECT   frequency, frequency - MIN(frequency) OVER() AS diff
FROM     words
ORDER BY frequency

Upvotes: 8

Related Questions