problem with union

Question

Post reply

problem with union

kayser.b

Grasshopper

Points: 21
More actions
March 27, 2013 at 2:26 am

#291394

Hi all,
I have a problem with my "union" query, this a sample of my query. What I'd like to obtain is my 'effectif_enseignant' and my 'effectif_biatss' in two separated columns. Can somebody help me please (I don't know if I'm writting in the good part of this formum so please appologise if it's not the correct area)
thank in advance
Bastien
select COUNT (perso_id)as 'effectif_enseignant',
REGION_NOM,
ACAD_NOM,
TYPO_NOM,
cat_perso_nom,
ETAB_RATT_NOM,
ZONE_GEO_NOM
from VIVIER
where CATEGORIE_PERSONNEL.CAT_PERSO_CODE ='ENS'and FLAG_ACTIVITE=1
union
select COUNT (perso_id) as 'effectif_biatss',
REGION_NOM,
ACAD_NOM,
TYPO_NOM,
cat_perso_nom,
ETAB_RATT_NOM,
ZONE_GEO_NOM
from VIVIER
where CATEGORIE_PERSONNEL.CAT_PERSO_CODE ='BIA' and FLAG_ACTIVITE=1

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

You will have to use JOINS for the same

I have used FULL OUTER JOIN in the code below( You can use INNER or LEFT OUTER as per your requirement)

; WITH cte_1 AS

(

select COUNT (perso_id)as 'effectif_enseignant',

REGION_NOM,

ACAD_NOM,

TYPO_NOM,

cat_perso_nom,

ETAB_RATT_NOM,

ZONE_GEO_NOM

from VIVIER

where CATEGORIE_PERSONNEL.CAT_PERSO_CODE ='ENS'and FLAG_ACTIVITE=1

), cte_2 AS

(

select COUNT (perso_id) as 'effectif_biatss',

REGION_NOM,

ACAD_NOM,

TYPO_NOM,

cat_perso_nom,

ETAB_RATT_NOM,

ZONE_GEO_NOM

from VIVIER

where CATEGORIE_PERSONNEL.CAT_PERSO_CODE ='BIA' and FLAG_ACTIVITE=1

)

SELECTCOALESCE(C1.REGION_NOM, C2.REGION_NOM) AS REGION_NOM,

COALESCE(C1.ACAD_NOM, C2.ACAD_NOM) AS ACAD_NOM,

COALESCE(C1.TYPO_NOM, C2.TYPO_NOM) AS TYPO_NOM,

COALESCE(C1.cat_perso_nom, C2.cat_perso_nom) AS cat_perso_nom,

COALESCE(C1.ETAB_RATT_NOM, C2.ETAB_RATT_NOM) AS ETAB_RATT_NOM,

COALESCE(C1.ZONE_GEO_NOM, C2.ZONE_GEO_NOM) AS ZONE_GEO_NOM,

COALESCE(effectif_enseignant,0) AS effectif_enseignant,

COALESCE(effectif_biatss,0) AS effectif_biatss

FROMcte_1 AS C1

FULL OUTER JOINcte_2 AS C2

ON C1.REGION_NOM = C2.REGION_NOM

AND C1.ACAD_NOM = C2.ACAD_NOM

AND C1.TYPO_NOM = C2.TYPO_NOM

AND C1.cat_perso_nom = C2.cat_perso_nom

AND C1.ETAB_RATT_NOM = C2.ETAB_RATT_NOM

AND C1.ZONE_GEO_NOM = C2.ZONE_GEO_NOM

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kayser.b Grasshopper Points: 21 More actions · Answer 2

Thank you very much for your reply I'm going to test this right now !

thanks again Bastien

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

kayser.b (3/27/2013)
Hi all,
I have a problem with my "union" query, this a sample of my query. What I'd like to obtain is my 'effectif_enseignant' and my 'effectif_biatss' in two separated columns. Can somebody help me please (I don't know if I'm writting in the good part of this formum so please appologise if it's not the correct area)
thank in advance
Bastien
select COUNT (perso_id)as 'effectif_enseignant',
REGION_NOM,
ACAD_NOM,
TYPO_NOM,
cat_perso_nom,
ETAB_RATT_NOM,
ZONE_GEO_NOM
from VIVIER
where CATEGORIE_PERSONNEL.CAT_PERSO_CODE ='ENS'and FLAG_ACTIVITE=1
union
select COUNT (perso_id) as 'effectif_biatss',
REGION_NOM,
ACAD_NOM,
TYPO_NOM,
cat_perso_nom,
ETAB_RATT_NOM,
ZONE_GEO_NOM
from VIVIER
where CATEGORIE_PERSONNEL.CAT_PERSO_CODE ='BIA' and FLAG_ACTIVITE=1

Can you post the whole query please? That includes the entire FROMlist and GROUP BY. Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kayser.b Grasshopper Points: 21 More actions · Answer 4

Thanks for your reply I have solved my problem but stilll this was the entire request !!

declare @region varchar(1200);

declare @academie varchar(1200);

declare @categorie_personnel varchar(1200);

declare @typologie varchar(1200);

declare @type_etablissement varchar (1200);

declare @zone_geo varchar(1200);

declare @etablissement varchar(1200);

set @region = '' ;

set @academie = '' ;

set @categorie_personnel = '' ;

set @typologie = '' ;

set @type_etablissement = '' ;

set @zone_geo = '' ;

set @etablissement = '' ;

select COUNT (perso_id)as 'effectif_enseignant',

0 AS 'biatss',

REGION_NOM,

ACAD_NOM,

TYPO_NOM,

cat_perso_nom,

ETAB_RATT_NOM,

ZONE_GEO_NOM,

TYPE_ETAB_RATT_NOM

from VIVIER

inner join GRADE on VIVIER.GRADE_CODE = GRADE.GRADE_CODE

inner join CORPS on GRADE.CORPS_CODE = CORPS.CORPS_CODE

inner join ETAB_RATT on VIVIER.ETAB_RATT_CODE = ETAB_RATT.ETAB_RATT_CODE

inner join CATEGORIE_PERSONNEL on CORPS.CAT_PERSO_CODE = CATEGORIE_PERSONNEL.CAT_PERSO_CODE

inner join TYPOLOGIE on ETAB_RATT.TYPO_CODE = TYPOLOGIE.TYPO_CODE

inner join ACADEMIE on ETAB_RATT.ACAD_CODE = ACADEMIE.ACAD_CODE

inner join REGION on ACADEMIE.REGION_CODE = REGION.REGION_CODE

inner join ZONE_GEO on ACADEMIE.ZONE_GEO_CODE = ZONE_GEO.ZONE_GEO_CODE

inner join TYPE_ETAB_RATT on ETAB_RATT.TYPE_ETAB_RATT_CODE = TYPE_ETAB_RATT.TYPE_ETAB_RATT_CODE

where CATEGORIE_PERSONNEL.CAT_PERSO_CODE ='ENS'and FLAG_ACTIVITE=1

and charindex(REGION_NOM +',', (case when isnull(@region, '') <> '' then @region else REGION_NOM end )+',')>=1

and charindex(ACAD_NOM +',', (case when isnull(@academie, '') <> '' then @academie else ACAD_NOM end )+',')>=1

and charindex(TYPO_NOM +',', (case when isnull(@typologie, '') <> '' then @typologie else TYPO_NOM end )+',')>=1

and charindex(cat_perso_nom +',', (case when isnull(@categorie_personnel, '') <> '' then @categorie_personnel else cat_perso_nom end )+',')>=1

and charindex(ETAB_RATT_NOM +',', (case when isnull(@etablissement , '') <> '' then @etablissement else ETAB_RATT_NOM end )+',')>=1

and charindex(ZONE_GEO_NOM +',', (case when isnull(@zone_geo , '') <> '' then @zone_geo else ZONE_GEO_NOM end )+',')>=1

and charindex(TYPE_ETAB_RATT_NOM +',', (case when isnull(@type_etablissement , '') <> '' then @type_etablissement else TYPE_ETAB_RATT_NOM end )+',')>=1

group by ZONE_GEO_NOM,REGION_NOM,ACAD_NOM,TYPOLOGIE.TYPO_NOM,CATEGORIE_PERSONNEL.CAT_PERSO_NOM,ETAB_RATT.ETAB_RATT_NOM,TYPE_ETAB_RATT_NOM

union

select 0 AS 'biatss',

COUNT (perso_id) as 'effectif_biatss',