• Replies 21 replies
  • Subscribers 340 subscribers
  • Views 56 views
  • Users 0 members are here
Related

bit of a Friday moment with some SQL

Christopher Hill
$organization



Hia all

 

I am having a bit of a Friday moment with some SQL and am hoping someone can show me the light…??

I am sure its very basic – but the finer details are some what eluding me today…

 

I have this script equivalent…

 

            Select

                        B.Description

                        Count(B.Description)

                        Sum(A.Amount)

            From

                        Table1 A

                        Join Table2 B on (A.id = B.id)

 

Where table A has one to many records of table B.

 

This runs fine, but what I am seeing is an incorrect Sum amount due to this one to many association between the two tables.

i.e. if an ID from Table A has £5 in the Amount column, and the same ID exists on 4 records on Table B – my sum result is coming back at £20, I only want a distinct £5.

 

My initial thought was to do something like putting Distinct in front of the A.Amount (Sum(Distinct A.Amount)) within the aggregate, but this groups by the money values, and not the ID…

 

I hope that makes sense to someone out there?!

Many thanks in advance!!

 

Christopher Hill

Swyddog Dadansoddi a Chymorth SQL

SQL Support Analyst

Canolfan Mileniwm Cymru

Wales Millennium Centre

 

Ebost\Email Christopher.Hill@wmc.org.uk

Ffon\Tel 029 2063 6372

 

www.wmc.org.uk

 

Dy Le Di

Make it Yours this year

 

Cysylltwch yn Gymraeg neu'n Saesneg fel dymunwch

Contact us in English or Welsh as you prefer

 

Plas Bute\Bute Place

Bae Caerdydd\Cardiff Bay

CF10 5AL

P Meddyliwch am yr amgylchedd cyn printio / Please consider the environment before printing this email

 



Mae’r ohebiaeth hon at ddefnydd y derbynnydd/derbynyddion bwriadedig yn unig. Os nad chi yw’r derbynnydd/derbynyddion bwriadedig, nodwch fod dosbarthu, copïo neu ddefnyddio’r ohebiaeth hon neu’r wybodaeth ynddi mewn unrhyw ffordd wedi ei wahardd yn gyfangwbl a gall fod yn anghyfreithlon. Os ydych wedi derbyn yr ohebiaeth hon trwy gamgymeriad a fyddech cystal â’i ddychwelyd i’r anfonwr. Yn yr achos hwn byddem yn ddiolchgar pe gallech hefyd anfon yr ohebiaeth at administrator@wmc.org.uk ac yna dileu’r e-bost a dinistrio unrhyw gopïau ohono. Cwmni cyfyngedig dan warrant, cofrestrwyd yng Nghymru a Lloegr. Rhif Cwmni 3221924. Rhif Elusen 1060458. Swyddfa gofrestredig: Plas Bute, Bae Caerdydd, Caerdydd CF10 3AL

This communication is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful.If you have received this communication in error please return it to the sender. In this event would be grateful if you would also copy the communication to administrator@wmc.org.uk then delete the email and destroy any copies of it. A company limited by guarantee, registered in England and Wales. Company number 3221924. Charity number 1060458. Registered office: Bute Place, Cardiff Bay, Cardiff CF10 5AL



[edited by: Todd Lantry at 1:44 PM (GMT -6) on 20 Jul 2009] added subject to post TL
Parents
  • $organization
    I will be out of the office starting 07/17/2009 and will not return until
    07/20/2009.

    I will respond to your message when I return.
  • Former Member
    Former Member $organization in reply to Jan LaRocque

    I  am trying to write a custom procedure and I know my question can easily be answered by some of you SQL gurus out there…

     

    Here is the code I have so far:

     

    select  cc.customer_no, cc.constituency, ct.description, ct.short_desc,

    c.fname, c.lname, st.department, st.title, st.hire_date, st.end_date, st.status

    from tx_const_cust cc

    join t_customer c

    on c.customer_no = cc.customer_no

    join tr_constituency ct

    on ct.id = cc.constituency

    join lt_MCFTA_STAFF st

    on st.customer_no = c.customer_no

    where cc.constituency in (48,49,50)

    and st.status = 'Active'

    order by c.lname

     

     

    I want to add in a CASE statement, so that the code pulls fname and lname when the n1n2_ind = 1, but pulls fname2 and lname2 instead, when the n1n2_ind = 2.  I am guessing I need something like the below, but am not sure how to format and where to insert the new code into the above statement:

     

    CASE c.fname, c.lname

    Where cc.n1n2_ind = 1

     

    ELSE c.fname2. c.lname2

    Where cc.n1n2_ind = 2

     

    Thanks in advance for help!

     

  • $organization in reply to Former Member

    Penny -

    Try this on for size:

    select  cc.customer_no, cc.constituency, ct.description, ct.short_desc,

    c.fname, c.lname, st.department, st.title, st.hire_date, st.end_date, st.status,

    CASE WHEN cc.n1n2_ind = 2 THEN 

    c.fname2 +', ' + c.lname2

    ELSE c.fname +', ' + c.lname

    END as full_name

    from tx_const_cust cc

    join t_customer c

    on c.customer_no = cc.customer_no

    join tr_constituency ct

    on ct.id = cc.constituency

    join lt_MCFTA_STAFF st

    on st.customer_no = c.customer_no

    where cc.constituency in (48,49,50)

    and st.status = 'Active'

    order by c.lname

    HTH,
    Heather

  • $organization in reply to Former Member

    Case when cc.n1n2_ind = 1 then c.fname Else c.fname2 end as FName,

    Case when cc.n1n2_ind = 2 then c.lname Else c.lname2 end as LName

     

     

    You also have an n1n2_ind of 3, what do you want to do in those cases?

     

     

     

     

    Marty Jones

    Database Administrator

     

    Omaha Performing Arts
    1200 Douglas Street

    Omaha, Nebraska 68102

    P 402.661.8469  |  F 402.345.0222    

    Marty.Jones@omahaperformingarts.org

    www.omahaperformingarts.org

    For tickets, call Ticket Omaha at 402.345.0606

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Penny Tabor
    Sent: Tuesday, September 07, 2010 3:23 PM
    To: Martin A. Jones
    Subject: [Tessitura Technical Forum] <No Subject>

     

    I  am trying to write a custom procedure and I know my question can easily be answered by some of you SQL gurus out there…

     

    Here is the code I have so far:

     

    select  cc.customer_no, cc.constituency, ct.description, ct.short_desc,

    c.fname, c.lname, st.department, st.title, st.hire_date, st.end_date, st.status

    from tx_const_cust cc

    join t_customer c

    on c.customer_no = cc.customer_no

    join tr_constituency ct

    on ct.id = cc.constituency

    join lt_MCFTA_STAFF st

    on st.customer_no = c.customer_no

    where cc.constituency in (48,49,50)

    and st.status = 'Active'

    order by c.lname

     

     

    I want to add in a CASE statement, so that the code pulls fname and lname when the n1n2_ind = 1, but pulls fname2 and lname2 instead, when the n1n2_ind = 2.  I am guessing I need something like the below, but am not sure how to format and where to insert the new code into the above statement:

     

    CASE c.fname, c.lname

    Where cc.n1n2_ind = 1

     

    ELSE c.fname2. c.lname2

    Where cc.n1n2_ind = 2

     

    Thanks in advance for help!

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • $organization in reply to Former Member

    Sorry, Error in the second one,

     

    Should be

     

    Case when cc.n1n2_ind = 1 then c.lname Else c.lname2 end as LName

     

     

    Marty Jones

    Database Administrator

     

    Omaha Performing Arts
    1200 Douglas Street

    Omaha, Nebraska 68102

    P 402.661.8469  |  F 402.345.0222    

    Marty.Jones@omahaperformingarts.org

    www.omahaperformingarts.org

    For tickets, call Ticket Omaha at 402.345.0606

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Marty Jones
    Sent: Tuesday, September 07, 2010 3:43 PM
    To: Martin A. Jones
    Subject: RE: [Tessitura Technical Forum] <No Subject>

     

    Case when cc.n1n2_ind = 1 then c.fname Else c.fname2 end as FName,

    Case when cc.n1n2_ind = 2 then c.lname Else c.lname2 end as LName

     

     

    You also have an n1n2_ind of 3, what do you want to do in those cases?

     

     

     

     

    Marty Jones

    Database Administrator

     

    Omaha Performing Arts
    1200 Douglas Street

    Omaha, Nebraska 68102

    P 402.661.8469  |  F 402.345.0222    

    Marty.Jones@omahaperformingarts.org

    www.omahaperformingarts.org

    For tickets, call Ticket Omaha at 402.345.0606

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Penny Tabor
    Sent: Tuesday, September 07, 2010 3:23 PM
    To: Martin A. Jones
    Subject: [Tessitura Technical Forum] <No Subject>

     

    I  am trying to write a custom procedure and I know my question can easily be answered by some of you SQL gurus out there…

     

    Here is the code I have so far:

     

    select  cc.customer_no, cc.constituency, ct.description, ct.short_desc,

    c.fname, c.lname, st.department, st.title, st.hire_date, st.end_date, st.status

    from tx_const_cust cc

    join t_customer c

    on c.customer_no = cc.customer_no

    join tr_constituency ct

    on ct.id = cc.constituency

    join lt_MCFTA_STAFF st

    on st.customer_no = c.customer_no

    where cc.constituency in (48,49,50)

    and st.status = 'Active'

    order by c.lname

     

     

    I want to add in a CASE statement, so that the code pulls fname and lname when the n1n2_ind = 1, but pulls fname2 and lname2 instead, when the n1n2_ind = 2.  I am guessing I need something like the below, but am not sure how to format and where to insert the new code into the above statement:

     

    CASE c.fname, c.lname

    Where cc.n1n2_ind = 1

     

    ELSE c.fname2. c.lname2

    Where cc.n1n2_ind = 2

     

    Thanks in advance for help!

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization in reply to Former Member

    Thanks to both Marty and Heather for your solutions…using a combo of both worked perfect!!!  It is so nice to have such knowledgeable people out there that can help so quickly!  Hope I can return the favor sometime!

     

     

    Penny Tabor

    IT Manager

    Midland Center for the Arts

    Midland, MI 48640

                                acerp

     

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Marty Jones
    Sent: Tuesday, September 07, 2010 5:58 PM
    To: Tabor, Penny
    Subject: RE: [Tessitura Technical Forum] <No Subject>

     

    Sorry, Error in the second one,

     

    Should be

     

    Case when cc.n1n2_ind = 1 then c.lname Else c.lname2 end as LName

     

     

    Marty Jones

    Database Administrator

     

    Omaha Performing Arts
    1200 Douglas Street

    Omaha, Nebraska 68102

    P 402.661.8469  |  F 402.345.0222    

    Marty.Jones@omahaperformingarts.org

    www.omahaperformingarts.org

    For tickets, call Ticket Omaha at 402.345.0606

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Marty Jones
    Sent: Tuesday, September 07, 2010 3:43 PM
    To: Martin A. Jones
    Subject: RE: [Tessitura Technical Forum] <No Subject>

     

    Case when cc.n1n2_ind = 1 then c.fname Else c.fname2 end as FName,

    Case when cc.n1n2_ind = 2 then c.lname Else c.lname2 end as LName

     

     

    You also have an n1n2_ind of 3, what do you want to do in those cases?

     

     

     

     

    Marty Jones

    Database Administrator

     

    Omaha Performing Arts
    1200 Douglas Street

    Omaha, Nebraska 68102

    P 402.661.8469  |  F 402.345.0222    

    Marty.Jones@omahaperformingarts.org

    www.omahaperformingarts.org

    For tickets, call Ticket Omaha at 402.345.0606

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Penny Tabor
    Sent: Tuesday, September 07, 2010 3:23 PM
    To: Martin A. Jones
    Subject: [Tessitura Technical Forum] <No Subject>

     

    I  am trying to write a custom procedure and I know my question can easily be answered by some of you SQL gurus out there…

     

    Here is the code I have so far:

     

    select  cc.customer_no, cc.constituency, ct.description, ct.short_desc,

    c.fname, c.lname, st.department, st.title, st.hire_date, st.end_date, st.status

    from tx_const_cust cc

    join t_customer c

    on c.customer_no = cc.customer_no

    join tr_constituency ct

    on ct.id = cc.constituency

    join lt_MCFTA_STAFF st

    on st.customer_no = c.customer_no

    where cc.constituency in (48,49,50)

    and st.status = 'Active'

    order by c.lname

     

     

    I want to add in a CASE statement, so that the code pulls fname and lname when the n1n2_ind = 1, but pulls fname2 and lname2 instead, when the n1n2_ind = 2.  I am guessing I need something like the below, but am not sure how to format and where to insert the new code into the above statement:

     

    CASE c.fname, c.lname

    Where cc.n1n2_ind = 1

     

    ELSE c.fname2. c.lname2

    Where cc.n1n2_ind = 2

     

    Thanks in advance for help!

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Reply
  • Former Member
    Former Member $organization in reply to Former Member

    Thanks to both Marty and Heather for your solutions…using a combo of both worked perfect!!!  It is so nice to have such knowledgeable people out there that can help so quickly!  Hope I can return the favor sometime!

     

     

    Penny Tabor

    IT Manager

    Midland Center for the Arts

    Midland, MI 48640

                                acerp

     

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Marty Jones
    Sent: Tuesday, September 07, 2010 5:58 PM
    To: Tabor, Penny
    Subject: RE: [Tessitura Technical Forum] <No Subject>

     

    Sorry, Error in the second one,

     

    Should be

     

    Case when cc.n1n2_ind = 1 then c.lname Else c.lname2 end as LName

     

     

    Marty Jones

    Database Administrator

     

    Omaha Performing Arts
    1200 Douglas Street

    Omaha, Nebraska 68102

    P 402.661.8469  |  F 402.345.0222    

    Marty.Jones@omahaperformingarts.org

    www.omahaperformingarts.org

    For tickets, call Ticket Omaha at 402.345.0606

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Marty Jones
    Sent: Tuesday, September 07, 2010 3:43 PM
    To: Martin A. Jones
    Subject: RE: [Tessitura Technical Forum] <No Subject>

     

    Case when cc.n1n2_ind = 1 then c.fname Else c.fname2 end as FName,

    Case when cc.n1n2_ind = 2 then c.lname Else c.lname2 end as LName

     

     

    You also have an n1n2_ind of 3, what do you want to do in those cases?

     

     

     

     

    Marty Jones

    Database Administrator

     

    Omaha Performing Arts
    1200 Douglas Street

    Omaha, Nebraska 68102

    P 402.661.8469  |  F 402.345.0222    

    Marty.Jones@omahaperformingarts.org

    www.omahaperformingarts.org

    For tickets, call Ticket Omaha at 402.345.0606

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Penny Tabor
    Sent: Tuesday, September 07, 2010 3:23 PM
    To: Martin A. Jones
    Subject: [Tessitura Technical Forum] <No Subject>

     

    I  am trying to write a custom procedure and I know my question can easily be answered by some of you SQL gurus out there…

     

    Here is the code I have so far:

     

    select  cc.customer_no, cc.constituency, ct.description, ct.short_desc,

    c.fname, c.lname, st.department, st.title, st.hire_date, st.end_date, st.status

    from tx_const_cust cc

    join t_customer c

    on c.customer_no = cc.customer_no

    join tr_constituency ct

    on ct.id = cc.constituency

    join lt_MCFTA_STAFF st

    on st.customer_no = c.customer_no

    where cc.constituency in (48,49,50)

    and st.status = 'Active'

    order by c.lname

     

     

    I want to add in a CASE statement, so that the code pulls fname and lname when the n1n2_ind = 1, but pulls fname2 and lname2 instead, when the n1n2_ind = 2.  I am guessing I need something like the below, but am not sure how to format and where to insert the new code into the above statement:

     

    CASE c.fname, c.lname

    Where cc.n1n2_ind = 1

     

    ELSE c.fname2. c.lname2

    Where cc.n1n2_ind = 2

     

    Thanks in advance for help!

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Children
No Data