3 Replies - 9030 Views - Last Post: 16 August 2011 - 08:42 AM

#1 DamienCurr  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 69
  • Joined: 03-May 09

SQL query issue

Posted 07 August 2011 - 03:38 PM

Good evening,

I have run into trouble with some queries I am writing. I am trying to calculate the total fees collected on each of three days of movie rentals. I used the following code that will give me three rentals from the day I specify, but it lists each individual cost instead of the total cost for the day.


SELECT SUM(r.total_fee), r.rental_id
FROM rentals r, rental_lines rl
WHERE r.rental_id=rl.rental_id
	AND date_in LIKE '08-AUG-11'
GROUP BY r.rental_id;




What I get is:

SUM(R.TOTAL_FEE) RENTAL_ID
---------------- ---------
4.7 R6
4.2 R5
4.2 R4

Not sure how to do this. Can any one direct me?

thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: SQL query issue

#2 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Re: SQL query issue

Posted 16 August 2011 - 08:17 AM

You might want to try making a subquery for it, something like this:

I can't promise this will work, but something along these lines

SELECT SUM(select total_fee from rentals) as Total, r.rental_id
FROM rentals r, rental_lines rl
WHERE r.rental_id=rl.rental_id
	AND date_in LIKE '08-AUG-11'
GROUP BY r.rental_id;




Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5641
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: SQL query issue

Posted 16 August 2011 - 08:28 AM

List the fields of each table and what you expect to see. You're not apparently doing anything with rental_lines, unless date_in is part of it. Avoid the table1, table2 syntax and use joins. date_in LIKE is probably not good.

Start by joining everything:
SELECT *
FROM rentals r
	INNER JOIN rental_lines rl
		ON r.rental_id=rl.rental_id
	WHERE trunc(r.date_in)='08-AUG-11'



Now, what data do you see and how can you tally it up to get what you want?


View PostBtu, on 16 August 2011 - 11:17 AM, said:

I can't promise this will work, but something along these lines


No, don't do that; ever. Where the hell is the join for that SUM subquery. How does it relate to anything. Even if it did, it's perhaps the slowest possible what to get data from a database.
Was This Post Helpful? 1
  • +
  • -

#4 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Re: SQL query issue

Posted 16 August 2011 - 08:42 AM

thanks Baavgai, good point :D
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1