User Name
Password

Go Back   Planetarion Forums > Non Planetarion Discussions > Programming and Discussion
Reload this Page help with my sql based project
Register FAQ Members List Calendar Arcade Today's Posts

Reply
View First Unread View First Unread  
Thread Tools Display Modes
Unread 25 Apr 2003, 13:53   #1
HOPupNdown
Last of the plushies
 
Join Date: Nov 2000
Location: israel
Posts: 91
HOPupNdown is an unknown quantity at this point
help with my sql based project
i have this project in databases im supposed to
make for uni , creating a car rental system using java
and SQL server databases .

one of the requirements is to create a query that
will output the 3 most profitable cars .

the data about car rental profits is in another table
called past_rentals , each record in the table there
contains 1 past rental and the sum cost of that rent .
cars are identified by their car number .
the table looks something like

car number|..junk data about rent..|total cost|



what query can i use to pick the 3 most profitable cars
from the past_rentals table ? , im using microsoft sql server
2k developer edition .

note :it can be done easily with reading the whole
sorted table into java and then running on it , but it must
be done in an SQL query , without using java ..
__________________
ZaRa
HOPupNdown is offline   Reply With Quote
Unread 25 Apr 2003, 14:18   #2
pablissimo
Henry Kelly
 
pablissimo's Avatar
 
Join Date: Apr 2000
Posts: 7,374
pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.pablissimo has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Are you just looking for the total cost field to base that on, or is it some kind of sum to do with that field and a fixed rental value?
__________________
You're now playing ketchup
pablissimo is offline   Reply With Quote
Unread 25 Apr 2003, 14:27   #3
meglamaniac
Born Sinful
 
meglamaniac's Avatar
 
Join Date: Nov 2000
Location: Loughborough, UK
Posts: 4,059
meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
If I understand you correctly, you need to sum all the entries for each car type in the past_rentals table in order to get the total rental for each car type yes?

I would do it simply by selecting all the entries where car type = type1 then summing in java, then where car type = type2, etc.

I've not got great SQL knowledge but I would suggest that doing the maths is more the realm of the programming language than the database interface.

__________________
Worth dying for. Worth killing for. Worth going to hell for. Amen.
meglamaniac is offline   Reply With Quote
Unread 25 Apr 2003, 14:45   #4
wu_trax
Registered User
 
Join Date: Jan 2003
Posts: 4,290
wu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet society
if i understood it correctly it would be something like
select car_number, sum(sum_costs) as sum from past_rentals order by sum
then just output the first 3 rows
__________________
im not tolerant, i just dont care.
wu_trax is offline   Reply With Quote
Unread 25 Apr 2003, 15:11   #5
Gayle29uk
Bitch
 
Join Date: Jun 2002
Location: North Yorkshire
Posts: 3,848
Gayle29uk is just really niceGayle29uk is just really niceGayle29uk is just really niceGayle29uk is just really nice
Quote:
Originally posted by wu_trax
then just output the first 3 rows
In mySQL I would do something like...

"SELECT car_number, sum(total_cost) AS sum ORDER BY sum DESC GROUP BY car_number LIMIT 0,3"

I think that would work (or something close).
__________________
ACHTUNG!!!
Das machine is nicht fur gefingerpoken und mittengrabben. Ist easy
schnappen der springenwerk, blowenfusen und corkenpoppen mit
spitzensparken. Ist nicht fur gewerken by das dummkopfen. Das
rubbernecken sightseeren keepen hands in das pockets. Relaxen und vatch
das blinkenlights!!!
Gayle29uk is offline   Reply With Quote
Unread 25 Apr 2003, 15:13   #6
wu_trax
Registered User
 
Join Date: Jan 2003
Posts: 4,290
wu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet society
yes, i thought of limit too, but then i thought thats my-sql-specific and wont work with ms sql
(oh, and yes, i forgot the group by, sorry about that )
__________________
im not tolerant, i just dont care.
wu_trax is offline   Reply With Quote
Unread 25 Apr 2003, 15:56   #7
HOPupNdown
Last of the plushies
 
Join Date: Nov 2000
Location: israel
Posts: 91
HOPupNdown is an unknown quantity at this point
is there a way in ms sql to select the top 3 without
using java ?

aside chosing the top 3 it works , thanks , i know
i shouldnt have cut databases classes to play pa
__________________
ZaRa
Last edited by HOPupNdown; 25 Apr 2003 at 16:01.
HOPupNdown is offline   Reply With Quote
Unread 25 Apr 2003, 16:01   #8
meglamaniac
Born Sinful
 
meglamaniac's Avatar
 
Join Date: Nov 2000
Location: Loughborough, UK
Posts: 4,059
meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Read Gayle's reply more carefully.

__________________
Worth dying for. Worth killing for. Worth going to hell for. Amen.
meglamaniac is offline   Reply With Quote
Unread 25 Apr 2003, 16:02   #9
HOPupNdown
Last of the plushies
 
Join Date: Nov 2000
Location: israel
Posts: 91
HOPupNdown is an unknown quantity at this point
alas , LIMIT does not exist in ms sql
__________________
ZaRa
HOPupNdown is offline   Reply With Quote
Unread 25 Apr 2003, 16:07   #10
wu_trax
Registered User
 
Join Date: Jan 2003
Posts: 4,290
wu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet society
ill get my notebook and try to figure it out, hold on...


(yes, im really bored )
__________________
im not tolerant, i just dont care.
wu_trax is offline   Reply With Quote
Unread 25 Apr 2003, 16:08   #11
Gayle29uk
Bitch
 
Join Date: Jun 2002
Location: North Yorkshire
Posts: 3,848
Gayle29uk is just really niceGayle29uk is just really niceGayle29uk is just really niceGayle29uk is just really nice
Quote:
Originally posted by meglamaniac
Read Gayle's reply more carefully.
Actually a little research shows I was wrong, LIMIT is a MySQL thing and not part of the SQL92 standard. MSSQL has a function to select the top 10 records...

SELECT TOP 10 car_number, sum(total_cost) AS sum ORDER BY sum DESC GROUP BY car_number

...but to get the top 3 you need to use stored procedures/temporary tables (apparently).

[edit] Apprently the original post I read was wrong, TOP takes an arbitrary argument so SELECT TOP 3 will give you the top 3 records. I leave this up to you to test as I don't have access to a MS SQL server [/edit]
__________________
ACHTUNG!!!
Das machine is nicht fur gefingerpoken und mittengrabben. Ist easy
schnappen der springenwerk, blowenfusen und corkenpoppen mit
spitzensparken. Ist nicht fur gewerken by das dummkopfen. Das
rubbernecken sightseeren keepen hands in das pockets. Relaxen und vatch
das blinkenlights!!!
Last edited by Gayle29uk; 25 Apr 2003 at 16:26.
Gayle29uk is offline   Reply With Quote
Unread 25 Apr 2003, 16:25   #12
wu_trax
Registered User
 
Join Date: Jan 2003
Posts: 4,290
wu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet societywu_trax is a pillar of this Internet society
TOP 3 works aswell.
__________________
im not tolerant, i just dont care.
wu_trax is offline   Reply With Quote
Unread 25 Apr 2003, 16:26   #13
Gayle29uk
Bitch
 
Join Date: Jun 2002
Location: North Yorkshire
Posts: 3,848
Gayle29uk is just really niceGayle29uk is just really niceGayle29uk is just really niceGayle29uk is just really nice
Quote:
Originally posted by wu_trax
TOP 3 works aswell.
Goddamn it, you did that as I edited :P
__________________
ACHTUNG!!!
Das machine is nicht fur gefingerpoken und mittengrabben. Ist easy
schnappen der springenwerk, blowenfusen und corkenpoppen mit
spitzensparken. Ist nicht fur gewerken by das dummkopfen. Das
rubbernecken sightseeren keepen hands in das pockets. Relaxen und vatch
das blinkenlights!!!
Gayle29uk is offline   Reply With Quote
Unread 25 Apr 2003, 20:55   #14
Raging.Retard
Street Tramp
 
Raging.Retard's Avatar
 
Join Date: Apr 2000
Location: Street Gutter
Posts: 341
Raging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant future
Oh dear.. a question actually on MSSQL and I missed it.
__________________
Chimney Pots.
Raging.Retard is offline   Reply With Quote
Reply

« Previous Thread | Programming and Discussion | Next Thread »


Forum Jump


All times are GMT +1. The time now is 22:24.
Planetarion - Top

Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2002 - 2018