Results 1 to 4 of 4
  1. #1
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066

    Transact SQL Sum

    I have a query I'm trying to design but not sure where to procede on 1 field

    The query links 2 tables a master and a detail table

    I need to write a query that accumulates the number of times a field in the detail table is > 0 for each record in the master table

    For example a master customer ID of 23 has 10 Detail Records I need to count how many of those detail records where field d is > 0

    In access it's simple

    Select masterid, Sum(IIF(Fieldd > 0,1,0))
    From mastertable inner join detailtable on mastertable.idfield = detailtable.idfield


    Where status = "1"
    Group By mastered
    Having Sum(IIF(Fieldd > 9,1,0)) > 1
    .
    Just not sure what to use in SQL for IIF

    Also this is from a vendor's database so I can't add functions or stored procedures.
    It has to be done in TSQL

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a query , Q1 , to count all master rec details.
    make Q2, that uses Q1 to only get the ones Count>0
    (select * from Q1 where [countOfDetail]>0)

    run Q2.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What do you mean by 'from a vendor's database' - where is this file?

    Why can't you add functions or procedures? Can't you build your own db that links to the tables?

    Did you try the IIf()? My research indicates it should work (available with SQLServer2012). Otherwise, I guess CASE WHEN THEN ELSE.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    the IIF() generated an error when I put it in TSQL. However the Case when then else worked perfectly thanks

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums