Results 1 to 3 of 3

Thread: Need help, urgent SQL query

  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Need help, urgent SQL query

    I have the following query:

    SELECT dbo.portinfo.PORTINFOID, Sum(dbo.loccvg.VALUEAMT) AS SumOfVALUEAMT, dbo.loccvg.LOCID
    FROM (dbo.loc INNER JOIN dbo.loccvg ON dbo.loc.LOCID = dbo.loccvg.LOCID) INNER JOIN (dbo.portinfo INNER JOIN dbo.portacct ON dbo.portinfo.PORTINFOID = dbo.portacct.PORTINFOID) ON dbo.loc.ACCGRPID = dbo.portacct.ACCGRPID
    GROUP BY dbo.portinfo.PORTINFOID, dbo.loccvg.LOCID
    HAVING (((dbo.portinfo.PORTINFOID)=73) AND ((Sum(dbo.loccvg.VALUEAMT))>=50000 And (Sum(dbo.loccvg.VALUEAMT))<=100000));

    The query works fine. It brings the following type of results:

    PORTINFOID SumofVALUEAMT LOCID
    73 1 210
    73 34 211
    73 20 333

    I would like to nest the above query into a query that will give me a total sum of SumofVALUEAMT field and a count of the LOCID field.

    So my result should look like this

    PORTINFOID SUM LOCID
    73 55 3

    thanks

  2. #2
    Join Date
    Oct 2010
    Posts
    2
    can someone help???

  3. #3
    Join Date
    Apr 2009
    Posts
    86
    mantra, without knowing your Database SQL I can only guess but this should work:

    Code:
    SELECT PORTINFOID, SUM(SUMOFVALUEAMT), COUNT(*) AS CNT FROM (
    
    SELECT dbo.portinfo.PORTINFOID, Sum(dbo.loccvg.VALUEAMT) AS SumOfVALUEAMT, dbo.loccvg.LOCID
    FROM (dbo.loc INNER JOIN dbo.loccvg ON dbo.loc.LOCID = dbo.loccvg.LOCID) INNER JOIN (dbo.portinfo INNER JOIN dbo.portacct ON dbo.portinfo.PORTINFOID = dbo.portacct.PORTINFOID) ON dbo.loc.ACCGRPID = dbo.portacct.ACCGRPID
    GROUP BY dbo.portinfo.PORTINFOID, dbo.loccvg.LOCID
    HAVING (((dbo.portinfo.PORTINFOID)=73) AND ((Sum(dbo.loccvg.VALUEAMT))>=50000 And (Sum(dbo.loccvg.VALUEAMT))<=100000))
    
    ) AS A
    GROUP BY PORTINFOID
    ;

Posting Permissions

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