a bit of simple maths with not so simple addon

[real_illusions]

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

[Ingolme]

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))

[Synook]

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.

[real_illusions]

Hmm..why is it a bad idea?Anyway...I would need the 2 lowest removed (so to speak) and the rest totalled up.Will see how those 2 queries work...