Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Reply
 
Thread Tools Search this Thread Display Modes
Old Feb 22, 2013, 05:07 PM   #1
ArtOfWarfare
macrumors 603
 
ArtOfWarfare's Avatar
 
Join Date: Nov 2007
Send a message via Skype™ to ArtOfWarfare
Math in SQL? (HW)

I haven't even had 3 hours of hands on time with SQL yet, so I'm sorry if this comes off sounding like a very noobish question, but I can't figure out what I need to do to take the number returned by this query:

Code:
SELECT description, deaths1 FROM who.deaths
INNER JOIN who.icd9
ON who.deaths.cause = who.icd9.cause
where sex = 2
and country in (SELECT country FROM who.country where country_name like '%united kingdom%')
and ryear = 1980
and deaths1 > 0
group by who.icd9.cause
order by deaths1 desc
limit 1;
And divide it by a number returned by a different query.

Specifically, the question from my homework assignment is
Quote:
What percentage of the United Kingdom's female population died in 1980 from the leading cause of death?
The query I wrote above returns what the leading cause of death is for the United Kingdom Females from 1980, but now I need to sum all of the deaths for the United Kingdom Females from 1980 and then divide it by that.

I can't make what I've already written a subquery, because MySQL complains that it doesn't support limits inside of subqueries.

Suggestions?

Edit:

Alright, I can separately find the sum of all UK female deaths from 1980 with this query:

Code:
SELECT SUM(deaths1) deaths1 FROM who.deaths
where sex = 2
and country in (SELECT country FROM who.country where country_name like '%united kingdom%')
and ryear = 1980;
I don't know how to divide one by the other, though.
__________________
Don't tell me Macs don't last: 2007 iMac, 2007 Mac Mini, 2008 MacBook Air, all Vintage.
(iMac obsoletion: April 28, 2015, MBA: October 14, 2015, Mac Mini: March 9, 2016)

Last edited by ArtOfWarfare; Feb 22, 2013 at 09:53 PM.
ArtOfWarfare is offline   0 Reply With Quote
Old Feb 22, 2013, 06:09 PM   #2
Ap0ks
macrumors regular
 
Join Date: Aug 2008
Location: Cambridge, UK
A schema and sample data would make this a lot easier to help

Do you have a query giving you the female population of the UK? From what I understand of the question you need the leading death cause figure divided by the total female population multiplied by 100. So something like:
Code:
SELECT description, deaths, (deaths/pop.population)*100 AS 'Percentage'
FROM deaths, (SELECT population FROM countries WHERE name = 'United Kingdom') AS pop
ORDER BY deaths DESC
LIMIT 1
Assuming you're given the population as a figure on the country table.
Ap0ks is offline   0 Reply With Quote
Old Feb 22, 2013, 08:51 PM   #3
ArtOfWarfare
Thread Starter
macrumors 603
 
ArtOfWarfare's Avatar
 
Join Date: Nov 2007
Send a message via Skype™ to ArtOfWarfare
Thanks!

I was able to figure out how to do this using the code you shared. My exact implementation doesn't look as nice (it's a lot more lines) but it gets the job done:

Code:
SELECT description, deaths1, pop1, (deaths1*100/pop1) AS 'Percent' FROM who.deaths
INNER JOIN who.icd9
ON who.deaths.cause = who.icd9.cause
INNER JOIN who.population
ON who.deaths.country = who.population.country
AND who.deaths.sex = who.population.sex
AND who.deaths.ryear = who.population.ryear
where who.deaths.sex = 2
and who.deaths.country in (SELECT country FROM who.country where country_name like '%united kingdom%')
and who.deaths.ryear = 1980
and who.deaths.deaths1 > 0
group by who.icd9.cause
order by deaths1 desc
limit 1;
The key thing I got from your code was that I can add my own field to the table by just putting a math formula in and then giving it a name with the AS keyword (or is that keyword even necessary? I think it might just accept the string after the equation as being a name for the column...)
__________________
Don't tell me Macs don't last: 2007 iMac, 2007 Mac Mini, 2008 MacBook Air, all Vintage.
(iMac obsoletion: April 28, 2015, MBA: October 14, 2015, Mac Mini: March 9, 2016)
ArtOfWarfare is offline   0 Reply With Quote
Old Feb 23, 2013, 03:23 AM   #4
Ap0ks
macrumors regular
 
Join Date: Aug 2008
Location: Cambridge, UK
Quote:
Originally Posted by ArtOfWarfare View Post
I was able to figure out how to do this using the code you shared. My exact implementation doesn't look as nice (it's a lot more lines) but it gets the job done
Glad you figured it out, my code was merely a basic example since I didn't have your full schema, hence the simplistic nature . I expected you'd need more lines to get it to work properly with your database.

Calculated columns are very useful and can be used to work out data when querying rather than having to store it in the database. I'm not a MySQL guy by trade so had to look it up in the documentation and you're correct, the AS keyword can be omitted when using aliases. I myself prefer leaving it in so I can easily see which fields I've given aliases, but each to their own I guess.
Ap0ks is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
Kids Garden Math - Learn Math While Harvesting Fruits PyjamasApps iPhone and iPod touch Apps 0 Jul 16, 2013 04:00 AM
Software similar to SQL Dev and SQL plus? doomfront Mac Basics and Help 0 Nov 20, 2012 12:25 PM
3rd Grade Math: Summer Review Splash Math App klkatielong iPad Apps 0 Jul 18, 2012 12:56 PM
[Math App] Homework teaches kids math and reports to parents Gaea deBomb iPhone and iPod touch Apps 0 Jul 3, 2012 04:35 AM
5+7+10+5+2+3= ? Math Aloud ! true mental math ... left & right brain. mobile.esd.mx iPhone and iPod touch Apps 0 Jun 1, 2012 04:17 PM

Forum Jump

All times are GMT -5. The time now is 06:04 AM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC