-
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
-
-
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
-
Forum Rules
|
|