SQL min function

Question

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;

Please show some sample data. The data you show seems fine: 1-1 = 0, 5-5 = 0. I'm sure that's not what you mean... — HoneyBadger
– HoneyBadger, Commented Dec 16, 2015 at 9:14
Are you using mysql or postgresql? Those are two very different databases. — Mureinik
– Mureinik, Commented Dec 16, 2015 at 9:14
I need minimum frequency of entire table. I guess it is considering min frequency of just that row — i_raqz
– i_raqz, Commented Dec 16, 2015 at 9:15

Mureinik · Accepted Answer · 2015-12-16 09:19:25Z

8

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

answered Dec 16, 2015 at 9:19

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jarlh · Accepted Answer · 2015-12-16 09:45:59Z

2

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

edited Dec 16, 2015 at 9:45

answered Dec 16, 2015 at 9:16

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

4 Comments

i_raqz Over a year ago

Ah... can we name the (select min(frequency) from words) and use it over and over again instead of writing the sub query?

i_raqz Over a year ago

SELECT frequency, frequency - minfreq, frequency + minfreq FROM words where minfreq = (select min(frequency) from words) ORDER BY frequency; Is this even possible?

splash58 Over a year ago

SELECT frequency, frequency - minfreq, frequency + minfreq FROM words, (select min(frequency) minfreq from words) t  ORDER BY frequency

jarlh Over a year ago

Added alternative, like splash58 suggested.

GarethD · Accepted Answer · 2015-12-16 09:24:22Z

1

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;

edited Dec 16, 2015 at 9:24

GarethD

70k11 gold badges90 silver badges130 bronze badges

answered Dec 16, 2015 at 9:20

Obaidul

575 bronze badges

Comments

Thorsten Kettner · Accepted Answer · 2015-12-16 09:42:40Z

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;

Collectives™ on Stack Overflow

SQL min function

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related