Using Group by functions in sub query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Group by functions in sub query

Author

Topic

rwasim25
Starting Member

13 Posts

Posted - 2010-09-22 : 00:14:49

Please consider below scenario i am stucked into it:

Table - A
Table - B
Table - C

There is a field premium in table A. Table A consists of policy details and one policy lets say (polA) is having more than 1 premium entry. We will require a consolidated result with record of all the three tables (A,B,C) in the results we will require the sum of all the premium entry in a single row for respective policy, Here we can't fetch the results without using group by function. But again there is three or four fields which have consolidated premium. (Actual premium, paid premium, market price..) we have to use the group by functions in every sub query and the results have to be in a consolidated formated. We are unable to use the group by in more than 1 fields...

It's urgent please help.. If require more details please let me know

Wasim..
Senior Test Engineer

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 02:45:21

[code]
SELECT A.Total1,B.Total2,C.Total3....
FROM
( -- apply GROUP BY on the first column & get the SUM
SELECT common_col,SUM( wharever_filed ) AS [Total1]
FROM tableA
WHERE
GROUP BY column_1
)A
INNER JOIN
(
-- apply GROUP BY on the second column & get the SUM
SELECT common_col,SUM( wharever_filed ) AS [Total2]
FROM tableA
WHERE
GROUP BY column_2

)B
ON A.common_col = B.common_col
INNER JOIN
(
-- apply GROUP BY on the third column & get the SUM
SELECT common_col,SUM( wharever_filed ) AS [Total3]
FROM tableA
WHERE
GROUP BY column_3

)C
ON B.common_col = C.common_col
INNER JOIN tableB D
ON C.common_col = D.common_col
INNER JOIN tableC E
ON D.common_col = E.common_col
[/code]

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 03:00:43

Can you please provide us with the table structures, sample data & expected output. That will help a lot

rwasim25
Starting Member

13 Posts

Posted - 2010-09-22 : 04:48:26

-- This quuery result needs to be in Market_Original_Premium_Amt field

Select sum(tst),aidnopef from(
select
(case
when ctyp in ('01','03','09','12') then uven
when ctyp in ('02', '04', '05', '07', '10') then -uven
when ctyp in ('11', '12') then 0
end ) as tst,
v.aidnopef
from
SIMM_TBYGSSC g,
SIMM_TBYVENT v,
cnv_policy_version c,
simm_tbyopef o,
simm_tbyvers t
where g.ccietec = v.ccietec
And g.lnumcon = v.lnumcon
And g.aidngssc = v.aidngssc
And g.ccietec = c.ccietec
And g.lnumcon = c.lnumcon
And g.nvrs = c.nvrs
AND g.ccietec = t.ccietec
AND g.lnumcon = t.lnumcon
AND g.nvrs = t.nvrs
AND o.deff>= t.deff
AND o.dfin<= t.dexp
And c.new_policy_category <> 'Excluded'
And v.aidnopef = o.aidnopef
And CCMP <> 99
AND g.ccietec in (70,74,85)
) q Group By aidnopef

-- This quuery result needs to be in Market_Settlement_Premium_Amt

Select sum(tst),aidnopef from(
select
(case
when ctyp in ('01','03','09','12') then uven
when ctyp in ('02', '04', '05', '07', '10') then -uven
when ctyp in ('11', '12') then 0
end ) as tst,
v.aidnopef
from
SIMM_TBYGSSC g,
SIMM_TBYVENT v,
cnv_policy_version c,
simm_tbyopef o,
simm_tbyvers t
where g.ccietec = v.ccietec
And g.lnumcon = v.lnumcon
And g.aidngssc = v.aidngssc
And g.ccietec = c.ccietec
And g.lnumcon = c.lnumcon
And g.nvrs = c.nvrs
AND g.ccietec = t.ccietec
AND g.lnumcon = t.lnumcon
AND g.nvrs = t.nvrs
AND o.deff>= t.deff
AND o.dfin<= t.dexp
And c.new_policy_category <> 'Excluded'
And v.aidnopef = o.aidnopef
And CCMP <> 99
AND g.ccietec in (70,74,85)
) q Group By aidnopef

Both this query is independentaly working fine with good results as total sum amount but merging this two fields have problen coz the rule of client is that we have to use the group by function for these cases (there are two similar fields as this) and all the four fields is having group by with aidnopef field. Merging all the queries is having problem.

Wasim..
Senior Test Engineer

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 05:15:46

i think you have posted the same query twice under the two headers

- This quuery result needs to be in Market_Original_Premium_Amt field &
-- This quuery result needs to be in Market_Settlement_Premium_Amt

i could not find any difference between the two

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-09-22 : 09:23:07

quote:
Originally posted by rwasim25

It's urgent please help.. If require more details please let me know

Wasim..
Senior Test Engineer

Homework?

Terry

-- -- Everyone is entitled to be stupid, but some abuse the privilege.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources