Sub Query Groupby Sum - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Sub Query Groupby Sum

Author

Topic

zain2
Starting Member

16 Posts

Posted - 2011-03-14 : 11:13:04

Hi everyone,

I am back again, I need to perform three level of queries and calculations,

Profiles contains group of FC's and FC's Contain LCs, First I need to select which LCs belongs to FC's and which FC's belong to Profiles and which profiles...

declare @xample table
   (    Profiles          varchar(3),
        FCs       varchar(4),
        LCs varchar(4)
   )
insert into @xample
select 'AAA', 'Test', 'HAE' union all
select 'BBB', 'Test'  'HAE'union all 
select 'AAA', 'Test'  'POS' union all
select 'CCC', 'Test'  'ZES'

 
select Profiles,
FCs,
LCs
from @xample

where Profiles
    = ( select FCs from @xample
        where FCs
    = (	select LCs from @xample
	where LCs = 'HAE')

I tried this code but no success.. First I am trying to retrieve the data and than sum(Profiles)->FC's ->LCs..

Any suggestions is greatly appreciated...

Thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-14 : 11:26:39

Have you tried this?


select Profiles,
FCs,
LCs
from @xample
where Profiles
    in ( select FCs from @xample
        where FCs
    in (	select LCs from @xample
	where LCs = 'HAE'))

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-14 : 11:42:14

It's not clear what you're looking for. What do you mean by which FCs that have LCs. The answer is that all of them do.
Maybe this?

SELECT Profiles
,SUM(CASE WHEN LCs= 'HAE' THEN 1 ELSE 0 END) as FCs
,SUM(CASE WHEN FCs= 'Test' THEN 1 ELSE 0 END) as LCs
from @xample
group by Profiles
having SUM(CASE WHEN LCs= 'HAE' THEN 1 ELSE 0 END) > 0
and SUM(CASE WHEN FCs= 'Test' THEN 1 ELSE 0 END) >0

Jim

Everyday I learn something that somebody else already knew

zain2
Starting Member

16 Posts

Posted - 2011-03-14 : 12:04:28

Madhavan

I tried but it doesn't bring any results either...

Have you tried this?


select Profiles,
FCs,
LCs
from @xample
where Profiles
    in ( select FCs from @xample
        where FCs
    in (	select LCs from @xample
	where LCs = 'HAE'))

zain2
Starting Member

16 Posts

Posted - 2011-03-14 : 12:42:16

Great it worked... just want to sum each fields...

zain2
Starting Member

16 Posts

Posted - 2011-03-16 : 09:48:13

IF OBJECT_ID('tempdb..#LCS') IS NOT NULL
BEGIN
DROP TABLE #LCS
END

CREATE TABLE #LCS
(
        Code       varchar(5),
        PGI   varchar(1),
        pgifc1 VARCHAR(5) Null,
        pgifc2 VARCHAR(5) Null,
        pgifc3 VARCHAR(5) Null,
        pgifc4 VARCHAR(5)Null
)
/* The scenario is like Active Directory - Profile - Groups - Users
  a profile contains multiple groups and group contains multiple items and items on its own all are stored in the same file.    
  */
insert into #LCS (Code, PGI, pgifc1,pgifc2,pgifc3,pgifc4)
VALUES
('IAC1','I', 'I123','','',''),
('PAC1','P', 'GAC1','GAC2','GAC10','GAC15'),
('GAC1','G', 'IAC1','IAC4',     '',''),
('GAC2','G', 'IAC3','IAC2', 'IAC1',''),
('IAC2','I', 'I567','','',''),
('PAC2','P', 'GAC3','','',''),
('IAC3','I', 'I890','','',''),
('GAC10','I', 'IAC23','IAC2','IAC10','IAC12')
GO
SELECT * FROM #LCS
GO

User request to print report for example Item I123, this should look for 'Code' if exists and than profiles, and Groups and display which profiles and groups got item I23...and display the relevant records...

Sorry for being ambigious. This it bit complicated for me to resolve the mystery...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources