Jump to content

a bit of simple maths with not so simple addon


real_illusions
 Share

Recommended Posts

Hi all,Is there an easy to way to add up all the numbers from a database but to discard the lowest?Each number is in its seperate field along with a name, for instance:Name 3 5 9 2 6 7Where I need to add them all up but discard the '2' as its the lowest number.Thanks :)

Link to comment
Share on other sites

What if there's more than one field with number 2?I think a query like this might work if you want to discard every single field that contains the lowest number.

SELECT SUM(field) AS total FROM (SELECT field FROM table HAVING field > MIN(field))

Link to comment
Share on other sites

Having homogeneous data spread across several fields is a very (very, very :)) bad idea - you should have another table, e.g.:Person (person_id, name)Number (person_id, number)And a one-to-many relationship between people and numbers.Anyway, as your schema stands you'd have to do something like:

SELECT number1 + number2 + number3 + number4 + number5 + number6 - (SELECT MIN(n) FROM (SELECT number1 n FROM table UNION SELECT number2 n FROM table UNION SELECT number3 n FROM table UNION SELECT number4 n FROM table UNION SELECT number5 n FROM table UNION SELECT number6 n FROM table)) FROM table

If you are using Oracle you can use WITH instead to cut down on the references to each field.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...