Jump to content
Sign in to follow this  
real_illusions

a bit of simple maths with not so simple addon

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

Share this post


Link to post
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))

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...