Solve : SQL question?

1.	Solve : SQL question?
Answer» I need to subtract 2 newly created columns from each other. Each customer has one or more orders and each order may (or may not) have a discount. So i have used the SUM and GROUP function to total the order prices and discounts. This has created 2 NEW columns called 'TOTAL' and 'DEDUCTION'. What i would like to do is subtract the 'DEDUCTION' from the 'TOTAL' to find the revenue generated, but i'm struggling to find a solution. Below is the sql that i have created so far, in case it helps. Quote SELECT ghh.member_id, SUM (ghl.daysgr.daily_rate) AS total, SUM (ghl.discount) AS deduction, pr.firstname, pr.surname, mem.registration_date, pr.person_id FROM garment_hire_header ghh, garment_hire_line ghl, garment_at_location gal, garment gr, membership mem, person pr WHERE ghh.hire_id=ghl.hire_id AND ghl.garment_at_branch_id=gal.garment_at_branch_id AND gal.garment_id=gr.garment_id AND ghh.member_id=mem.member_id AND mem.person_id=pr.person_id GROUP BY ghh.member_id, pr.firstname, pr.surname,mem.registration_date, pr.person_id ORDER BY total DESC; The highlighted part is the SUM functions which are creating the new columns. Any ideas?Er... Additional element in SELECT statement of: Code: [Select] (SUM(ghl.daysgr.daily_rate) - SUM(ghl.discount)) AS revenue, Is that what you meant? Are you sure those SUM functions are working the way you expect? Quote from: Rob Pomeroy on May 04, 2012, 02:29:06 AM Are you sure those SUM functions are working the way you expect? Yeah, the SUM functions are working. I've got all the data in spreadsheets so that i can do some filtering and addition to check against the SQL queries. I'll try what you've suggested a little later, this is just a quick stop. Quote from: reddevilggg on May 04, 2012, 06:08:59 AM I've got all the data in spreadsheets so that i can do some filtering and addition to check against the SQL queries. Very sensible. I'll freely admit that my query design is often a trial and error affair - especially when it involves complex joins. Code: [Select](SUM(ghl.days*gr.daily_rate) - SUM(ghl.discount)) AS revenue Yes Rob, that works perfect. I'm finding it quite strange because, when you post a reply, the answer seems obvious. I seem to reach a point when the SQL gets to a certain size and i become STUMPED.......No PROBLEM. Have you installed MySQL Workbench? I use it all the time for MySQL query design. http://www.mysql.com/products/workbench/ For queries like the following, I just have to keep adding bits and checking the results as I go along, against a sample dataset. I can do the SQL design in theory, but there's nothing quite like seeing "live" results to provide confidence that you're coding correctly. MySQL Workbench is brilliant for that. Hit ctrl-enter and it runs the query that you're currently designing, GIVING you a table showing the results or generating errors as appropriate: Code: [Select]select emails_metadata.id, emails_metadata.`user`, emails_metadata.`name`, emails_metadata.domain_id AS primary_domain_id, emails_metadata.`type`, emails_metadata.region, emails_metadata.location, emails_metadata.company, emails_metadata.pcm_for, emails_metadata.permitted_sender, domains.`name` AS backup_domain_name, `01_02`, `91` from emails_metadata left join domains_obsidian ON emails_metadata.domain_id = domains_obsidian.backup_for left join domains ON domains_obsidian.domain_id = domains.id left join maillist_senders ON emails_metadata.id = maillist_senders.email Three left joins. No MATTER how good you are, it's almost impossible to hold it all in your head and remember what the entire query's supposed to be doing... That looks good, i'll check it out. Yes, i ususally start with basic 'ingredients', then keep adding and checking, but like you say, it gets to a certain size and it starts getting away from me. Thanks for the link

Answer»

I need to subtract 2 newly created columns from each other. Each customer has one or more orders and each order may (or may not) have a discount. So i have used the SUM and GROUP function to total the order prices and discounts. This has created 2 NEW columns called 'TOTAL' and 'DEDUCTION'.

What i would like to do is subtract the 'DEDUCTION' from the 'TOTAL' to find the revenue generated, but i'm struggling to find a solution. Below is the sql that i have created so far, in case it helps.

Quote

SELECT ghh.member_id, SUM (ghl.days*gr.daily_rate) AS total, SUM (ghl.discount) AS deduction, pr.firstname, pr.surname, mem.registration_date, pr.person_id

FROM garment_hire_header ghh, garment_hire_line ghl, garment_at_location gal, garment gr, membership mem, person pr

WHERE ghh.hire_id=ghl.hire_id
AND ghl.garment_at_branch_id=gal.garment_at_branch_id
AND gal.garment_id=gr.garment_id
AND ghh.member_id=mem.member_id
AND mem.person_id=pr.person_id

GROUP BY ghh.member_id, pr.firstname, pr.surname,mem.registration_date, pr.person_id
ORDER BY total DESC;

The highlighted part is the SUM functions which are creating the new columns.

Any ideas?Er... Additional element in SELECT statement of:

Code: [Select] (SUM(ghl.days*gr.daily_rate) - SUM(ghl.discount)) AS revenue,
Is that what you meant?

Are you sure those SUM functions are working the way you expect?
Quote from: Rob Pomeroy on May 04, 2012, 02:29:06 AM

Are you sure those SUM functions are working the way you expect?

Yeah, the SUM functions are working. I've got all the data in spreadsheets so that i can do some filtering and addition to check against the SQL queries.

I'll try what you've suggested a little later, this is just a quick stop. Quote from: reddevilggg on May 04, 2012, 06:08:59 AM

I've got all the data in spreadsheets so that i can do some filtering and addition to check against the SQL queries.

Very sensible. I'll freely admit that my query design is often a trial and error affair - especially when it involves complex joins.
Code: [Select](SUM(ghl.days*gr.daily_rate) - SUM(ghl.discount)) AS revenue
Yes Rob, that works perfect. I'm finding it quite strange because, when you post a reply, the answer seems obvious. I seem to reach a point when the SQL gets to a certain size and i become STUMPED.......No PROBLEM. Have you installed MySQL Workbench? I use it all the time for MySQL query design.

http://www.mysql.com/products/workbench/

For queries like the following, I just have to keep adding bits and checking the results as I go along, against a sample dataset. I can do the SQL design in theory, but there's nothing quite like seeing "live" results to provide confidence that you're coding correctly. MySQL Workbench is brilliant for that. Hit ctrl-enter and it runs the query that you're currently designing, GIVING you a table showing the results or generating errors as appropriate:

Code: [Select]select emails_metadata.id, emails_metadata.`user`,
emails_metadata.`name`, emails_metadata.domain_id AS primary_domain_id,
emails_metadata.`type`, emails_metadata.region, emails_metadata.location,
emails_metadata.company, emails_metadata.pcm_for,
emails_metadata.permitted_sender,
domains.`name` AS backup_domain_name,
`01_02`, `91`
from emails_metadata
left join domains_obsidian ON emails_metadata.domain_id = domains_obsidian.backup_for
left join domains ON domains_obsidian.domain_id = domains.id
left join maillist_senders ON emails_metadata.id = maillist_senders.email
Three left joins. No MATTER how good you are, it's almost impossible to hold it all in your head and remember what the entire query's supposed to be doing...
That looks good, i'll check it out.

Yes, i ususally start with basic 'ingredients', then keep adding and checking, but like you say, it gets to a certain size and it starts getting away from me. Thanks for the link

Solve : SQL question?

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment