Page 1 of 2 12 LastLast
Results 1 to 16 of 31

Thread: More SQL

  1. #1
    Hmmm bed
    Join Date
    Jul 2003
    Posts
    441
    Thanks
    5
    Thanked
    0 times in 0 posts

    More SQL

    Right, last one now, here's something else i can't seem to get working,

    It involves a few tables, what i want to do is calculate the total amount of money made from each CD (they're hired out), i think it'll need to involve the following ...

    table:cd1
    cataloguenumber
    title
    dailycost

    table:stock1
    stockid
    cataloguenumber
    purchasecost
    purchasedate

    table:rentalhead1
    rentalnumber
    memberid
    dateofrental
    staffname
    sig

    table:rentalline1
    rentalnumber
    stockid
    returndate

    right, so obviously, the date the cd was taken out, the date it was brought back in tied in with the daily cost will give us the total cost per hire, but its just the totaling and grouping i can't get my head round

    thanks for your help


  2. #2
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    SELECT cd1.title, cd1.dailycost*DATEDIFF(rentalline1.returndate,rentalhead1.dateofrental) AS profit
    FROM cd1, rentalline, rentalhead, stock1
    WHERE cd1.cataloguenumber=stock1.cataloguenumber AND stock1.stockid=rentalline1.stockid AND rentalline1.rentalnumber=rentalhead1.rentalnumber

    Since I don't have your tables and I cant be bothered setting them up then I can't test it but should be roughly right. If it isn't then export the SQL and data for your tables and send it to az@hexus.org and I'll set up the tables here and fix the issue

  3. #3
    Hmmm bed
    Join Date
    Jul 2003
    Posts
    441
    Thanks
    5
    Thanked
    0 times in 0 posts
    hey, cheers muchly again for the help, got i working finally like this:
    SELECT distinct title, sum((returndate - dateofrental)*dailycost) as totalprice
    FROM member1, cd1, rentalhead1, rentalline1, stock1
    WHERE cd1.cataloguenumber = stock1.cataloguenumber
    AND stock1.stockid = rentalline1.stockid
    AND rentalline1.rentalnumber = rentalhead1.rentalnumber
    GROUP BY by title;

  4. #4
    Senior Member GAteKeeper's Avatar
    Join Date
    Feb 2004
    Location
    Derbyshire, UK
    Posts
    582
    Thanks
    14
    Thanked
    28 times in 22 posts
    • GAteKeeper's system
      • Motherboard:
      • MSI P67-GD5
      • CPU:
      • Intel i7 2600k
      • Memory:
      • 8Gb Corsair DDR3 1600
      • Storage:
      • ~44TB
      • Graphics card(s):
      • 980Ti
      • PSU:
      • Seasonic S12 600W
      • Case:
      • Lian Li PC-65
      • Operating System:
      • Win10 64bit
      • Monitor(s):
      • Dell U3415W & 2405fpw
      • Internet:
      • 45Mb vDSL
    As a side note:

    SQL requires that anything that isnt in an agreegate function (sum, first, min , max count etc) is included in its original form (not the synomyn if you have one setup, eg table1.columnX instead of "Parts Made") in the groupby part of the query. This can cause mucho problems.

    I hate the way MS Access and SQL Server use the INNER/LEFT/RIGHT Join syntax and oracle et al use 'from table1,table2 where table1.id = table2.id'. I sometimes use access to build queries against an oracle dB for creating a view and having to re-code the query is a pain.

    GAteKeeper
    Keeper of the Gates of Hell

  5. #5
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    Quote Originally Posted by GAteKeeper
    As a side note:

    SQL requires that anything that isnt in an agreegate function (sum, first, min , max count etc) is included in its original form (not the synomyn if you have one setup, eg table1.columnX instead of "Parts Made") in the groupby part of the query. This can cause mucho problems.
    Depends on the database (DBMS that is) you're using. MySQL certainly does not (though if you do specify it the query will still work IIRC)

    Quote Originally Posted by GAteKeeper
    I hate the way MS Access and SQL Server use the INNER/LEFT/RIGHT Join syntax and oracle et al use 'from table1,table2 where table1.id = table2.id'. I sometimes use access to build queries against an oracle dB for creating a view and having to re-code the query is a pain.

    GAteKeeper
    You don't have to use join syntax in any DBMS AFAIK - and I know from experience that Oracle, MySQL and DB2 all accept join syntax too. Maybe you've been using old versions somewhere?

  6. #6
    Senior Member GAteKeeper's Avatar
    Join Date
    Feb 2004
    Location
    Derbyshire, UK
    Posts
    582
    Thanks
    14
    Thanked
    28 times in 22 posts
    • GAteKeeper's system
      • Motherboard:
      • MSI P67-GD5
      • CPU:
      • Intel i7 2600k
      • Memory:
      • 8Gb Corsair DDR3 1600
      • Storage:
      • ~44TB
      • Graphics card(s):
      • 980Ti
      • PSU:
      • Seasonic S12 600W
      • Case:
      • Lian Li PC-65
      • Operating System:
      • Win10 64bit
      • Monitor(s):
      • Dell U3415W & 2405fpw
      • Internet:
      • 45Mb vDSL
    nope got 9i installed on the development server - need to reboot it and stick the extra 600Mb of ram i have just got in its not liking only having 512 and running windows :-( . I know its valid syntax but its been my experience that you cant just cut and paste - it doesnt seem to like it.

    As for the agreagate functions - its good practise to follow ANSI standards which i think require the group by being correct.

    GAteKeeper
    Keeper of the Gates of Hell

  7. #7
    Hmmm bed
    Join Date
    Jul 2003
    Posts
    441
    Thanks
    5
    Thanked
    0 times in 0 posts
    here's another one for you! I want to say, select people who have introduced more than 1 person to the company. ... got this but can't see the problem with it


    SELECT member.surname MemberSurname, member.firstname MemberFirstname, member.memberid, intro.surname IntroducedBySurname, intro.firstname IntroducedByFirstName, intro.memberid,count(intro.memberid)
    FROM member1 member, member1 intro
    WHERE member.introducedby = intro.memberid(+)
    GROUP BY member.surname,member.firstname,member.memberid,intro.surname,intro.firstname,intro.memberid
    HAVING count(*)>1

    any help would be fab, thanks :-)

  8. #8
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    Quote Originally Posted by Basher
    here's another one for you! I want to say, select people who have introduced more than 1 person to the company. ... got this but can't see the problem with it


    SELECT member.surname MemberSurname, member.firstname MemberFirstname, member.memberid, intro.surname IntroducedBySurname, intro.firstname IntroducedByFirstName, intro.memberid,count(intro.memberid)
    FROM member1 member, member1 intro
    WHERE member.introducedby = intro.memberid(+)
    GROUP BY member.surname,member.firstname,member.memberid,intro.surname,intro.firstname,intro.memberid
    HAVING count(*)>1

    any help would be fab, thanks :-)

    Couple of problems off the top of my head:

    1) You won't get a single row back for each introducer if you include details of the people they introduced in the select (the reference in the where clause is OK)...

    2) The "count(*)" for the having clause is not the same as the "count(intro.memberid)

    3) Do you have to do the whole thing in one query? If you're not an SQL expert you could use 2 simpler queries to do the same thing (assuming this is being run in some kind of program / script and not interactively)... For example:

    Code:
    select introducedby, count(*) from member
    group by introducedby
    having count(*) > 1
    A nice simple query - though I'd also consider adding "order by 2 desc" so that it gives you the highest / most loyal customers first

    Then for each row in the first resultset / recordset you could do:

    Code:
    "select surname, firstname, memberid from member where memberid = " + introducedby
    (assuming you'd stored the introducedby field from the 1st query in that variable for each row...)

    If you're only doing the query interactively you could also do it in a subselect IIRC (been ages since I've done anything with Oracle - it was Oracle you're using right?)...
    Last edited by malfunction; 12-01-2005 at 06:53 PM.

  9. #9
    Senior Member GAteKeeper's Avatar
    Join Date
    Feb 2004
    Location
    Derbyshire, UK
    Posts
    582
    Thanks
    14
    Thanked
    28 times in 22 posts
    • GAteKeeper's system
      • Motherboard:
      • MSI P67-GD5
      • CPU:
      • Intel i7 2600k
      • Memory:
      • 8Gb Corsair DDR3 1600
      • Storage:
      • ~44TB
      • Graphics card(s):
      • 980Ti
      • PSU:
      • Seasonic S12 600W
      • Case:
      • Lian Li PC-65
      • Operating System:
      • Win10 64bit
      • Monitor(s):
      • Dell U3415W & 2405fpw
      • Internet:
      • 45Mb vDSL
    Basher, if you include the Member and the introducee on each line then you will have no grouping and your query makes no sense.

    If all you want to do is to select The member and the number of introduced persons then its more like this:

    Code:
     
    SELECT member.memberid, member.surname MemberSurname, member.firstname MemberFirstname, count(intro.memberid) as MembersIntroduced
    FROM member1 member, member1 intro
    WHERE member.introducedby = intro.memberid
    GROUP BY member.surname , member.firstname, member.memberid
    If you want to see all the people that have been introduced by members who have introduced more than one person then

    Code:
     
    SELECT member.surname MemberSurname, member.firstname MemberFirstname, member.memberid, intro.surname IntroducedBySurname, intro.firstname IntroducedByFirstName, intro.memberid
    FROM member1 member, member1 intro
    WHERE member.introducedby = intro.memberid
    and member.memberid in (SELECT membersub.memberid
    FROM member1 membersub, member1 introsub
    WHERE membersub.introducedby = introsub.memberid
    and count(introsub.memberid)>1)
    Although having the table joined to itself is making it hard to make sure the SQL is correct i think these are ok.

    Why are you using outer joins? (the (+) bit) - since you are only interested in members who have introduced people this makes no sense

    GK
    Keeper of the Gates of Hell

  10. #10
    Hmmm bed
    Join Date
    Jul 2003
    Posts
    441
    Thanks
    5
    Thanked
    0 times in 0 posts
    That's brill thanks for the help, am using

    select introducedby, count(*) from member1
    group by introducedby
    having count(*) > 1

    to display the ID of the people and the number of people they introduced of people who introduced more than 1 person. From this how would I go about also displaying there names? I'm guessing a self join of sorts?

    Thanks again.

  11. #11
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    I don't have access to a decent database server at the mo but it would be something like this off the top of my head:

    select parent.memberid, parent.surname, parent.firstname, count(child.memberid) from member1 parent, member1 child
    where child.introducedby = parent.memberid
    group by parent.memberid, parent.surname, parent.firstname

    I've called them parent and child as I found it MUCH easier to think about that way - could be totally wrong though as I don't have access to a server at the mo. Might have to be done with a subselect. It's basically what gatekeeper said above but I think he got the parent child thing the wrong way around (basically have a play around with it as it's deffo something along those lines). If you're REALLY stuck ask again tomorrow as I will have my server back together by then.

  12. #12
    Senior Member GAteKeeper's Avatar
    Join Date
    Feb 2004
    Location
    Derbyshire, UK
    Posts
    582
    Thanks
    14
    Thanked
    28 times in 22 posts
    • GAteKeeper's system
      • Motherboard:
      • MSI P67-GD5
      • CPU:
      • Intel i7 2600k
      • Memory:
      • 8Gb Corsair DDR3 1600
      • Storage:
      • ~44TB
      • Graphics card(s):
      • 980Ti
      • PSU:
      • Seasonic S12 600W
      • Case:
      • Lian Li PC-65
      • Operating System:
      • Win10 64bit
      • Monitor(s):
      • Dell U3415W & 2405fpw
      • Internet:
      • 45Mb vDSL
    That looks remarkably the same as my first peice of SQL in the above post - so it must be right ;-) .

    GK
    Keeper of the Gates of Hell

  13. #13
    Hmmm bed
    Join Date
    Jul 2003
    Posts
    441
    Thanks
    5
    Thanked
    0 times in 0 posts
    Quote Originally Posted by malfunction
    I don't have access to a decent database server at the mo but it would be something like this off the top of my head:

    select parent.memberid, parent.surname, parent.firstname, count(child.memberid) from member1 parent, member1 child
    where child.introducedby = parent.memberid
    group by parent.memberid, parent.surname, parent.firstname

    I've called them parent and child as I found it MUCH easier to think about that way - could be totally wrong though as I don't have access to a server at the mo. Might have to be done with a subselect. It's basically what gatekeeper said above but I think he got the parent child thing the wrong way around (basically have a play around with it as it's deffo something along those lines). If you're REALLY stuck ask again tomorrow as I will have my server back together by then.
    yeah that works brill, great, thanks for all your help :-)

  14. #14
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    Quote Originally Posted by GAteKeeper
    That looks remarkably the same as my first peice of SQL in the above post - so it must be right ;-) .

    GK
    Yes - read it through again and we've both said the same thing... I'm not really keen on using the same table twice in a query... It's just confusing

  15. #15
    Hmmm bed
    Join Date
    Jul 2003
    Posts
    441
    Thanks
    5
    Thanked
    0 times in 0 posts
    One slightly different one now, i wish to select the members who have spent more than the average amount in total, now i have this query to get everyone's total spend ...

    SELECT distinct member1.memberid, firstname,surname,sum((returndate - dateofrental)*dailycost) as amountSpent
    FROM member1, cd1, rentalhead1, rentalline1, stock1
    WHERE cd1.cataloguenumber = stock1.cataloguenumber
    AND stock1.stockid = rentalline1.stockid
    AND rentalline1.rentalnumber = rentalhead1.rentalnumber
    AND rentalhead1.memberid = member1.memberid
    GROUP BY member1.memberid,firstname,surname;

    so i'm assuming i need to incorporate this into a subquery? Been playing about with it for a while but to no avail,

    again, any help is great :-)

    cheers guys.

  16. #16
    Senior Member GAteKeeper's Avatar
    Join Date
    Feb 2004
    Location
    Derbyshire, UK
    Posts
    582
    Thanks
    14
    Thanked
    28 times in 22 posts
    • GAteKeeper's system
      • Motherboard:
      • MSI P67-GD5
      • CPU:
      • Intel i7 2600k
      • Memory:
      • 8Gb Corsair DDR3 1600
      • Storage:
      • ~44TB
      • Graphics card(s):
      • 980Ti
      • PSU:
      • Seasonic S12 600W
      • Case:
      • Lian Li PC-65
      • Operating System:
      • Win10 64bit
      • Monitor(s):
      • Dell U3415W & 2405fpw
      • Internet:
      • 45Mb vDSL
    Right you need to write the query that gets you the average.

    Then put that in as a where clause:
    select blah,blah etc
    from ...
    where sum(x*y*z) > (select average(a*b*c) as average from .......)

    GateKeeper
    Keeper of the Gates of Hell

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. tell me about SQL?
    By Crazy Fool in forum Software
    Replies: 5
    Last Post: 07-01-2005, 12:46 AM
  2. My SQL, circular, inefficient ... or neither?
    By Anders in forum Software
    Replies: 14
    Last Post: 17-11-2004, 10:27 PM
  3. Oracle SQL
    By Raz316 in forum Software
    Replies: 7
    Last Post: 08-06-2004, 02:20 PM
  4. Learning SQL
    By Lead_Head in forum Software
    Replies: 13
    Last Post: 26-05-2004, 06:54 PM
  5. SQL help
    By Agent in forum Software
    Replies: 23
    Last Post: 11-11-2003, 09:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •