[T-SQL] Constante dans un group by
Bonjour,
Je travaille sur une requête pour aggréger des données de 3 tables différentes avec une structure identique (sur SQL Server 2000). La requete que j'utilise sélectionne les données parmi une union de 3 select sur les 3 tables précédemment citées.
La voici :
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| SELECT '200707', 'P', charge_code,SUM(charge/1.15) as charge, SUM(bonus) as bonus, SUM(duration) as duration,SUM(calls) calls
FROM (
SELECT charge_code, SUM(CHANGE_AMOUNT_1) as charge,
SUM(CHANGE_AMOUNT_2*0.12/1.15+CHANGE_AMOUNT_3/1.15+change_amount_4*1.99/1.15+change_amount_5*0.12/1.15+change_amount_6/1024/1024*2.5/1.15+change_amount_7*1.99/1.15+change_amount_8*0.09/60/1.15+change_amount_9*0.12/1.15) as bonus,
SUM(USAGE_AMOUNT) as duration,count(*) calls
FROM dbo.CALL_HISTORY_HIST_200707 t
WHERE t.COS_ID IN (SELECT COS FROM COS_LINK WHERE (product_type = 'PREP') AND (PRODUCT_LINE = 'Mobile'))
GROUP BY charge_code
UNION ALL
SELECT charge_code, SUM(CHANGE_AMOUNT_1) as charge,
SUM(CHANGE_AMOUNT_2*0.12/1.15+CHANGE_AMOUNT_3/1.15+change_amount_4*1.99/1.15+change_amount_5*0.12/1.15+change_amount_6/1024/1024*2.5/1.15+change_amount_7*1.99/1.15+change_amount_8*0.09/60/1.15+change_amount_9*0.12/1.15) as bonus,
SUM(USAGE_AMOUNT) as duration,count(*) calls
FROM dbo.OSA_HISTORY_HIST_200707 t
WHERE t.COS_ID IN (SELECT COS FROM COS_LINK WHERE (product_type = 'PREP') AND (PRODUCT_LINE = 'Mobile'))
GROUP BY charge_code
UNION ALL
SELECT charge_code, SUM(CHANGE_AMOUNT_1) as charge,
SUM(CHANGE_AMOUNT_2*0.12/1.15+CHANGE_AMOUNT_3/1.15+change_amount_4*1.99/1.15+change_amount_5*0.12/1.15+change_amount_6/1024/1024*2.5/1.15+change_amount_7*1.99/1.15+change_amount_8*0.09/60/1.15+change_amount_9*0.12/1.15) as bonus,
SUM(USAGE_AMOUNT) as duration,count(*) calls
FROM dbo.PS_TRANSACTION_HIST_200707 t
WHERE t.COS_ID IN (SELECT COS FROM COS_LINK WHERE (product_type = 'PREP') AND (PRODUCT_LINE = 'Mobile'))
GROUP BY charge_code) as DATA
GROUP BY charge_code |
J'ai maintenant besoin de rajouter une information dans chaque sous requête, il s'agit d'un bête entier. Ce qui donnerait (pour une seule sous requête), ceci :
Code:
1 2 3 4 5 6
| SELECT 1 as service_code, charge_code, SUM(CHANGE_AMOUNT_1) as charge,
SUM(CHANGE_AMOUNT_2*0.12/1.15+CHANGE_AMOUNT_3/1.15+change_amount_4*1.99/1.15+change_amount_5*0.12/1.15+change_amount_6/1024/1024*2.5/1.15+change_amount_7*1.99/1.15+change_amount_8*0.09/60/1.15+change_amount_9*0.12/1.15) as bonus,
SUM(USAGE_AMOUNT) as duration,count(*) calls
FROM dbo.CALL_HISTORY_HIST_200707 t
WHERE t.COS_ID IN (SELECT COS FROM COS_LINK WHERE (product_type = 'PREP') AND (PRODUCT_LINE = 'Mobile'))
GROUP BY charge_code, service_code |
Problème : SQL Server me renvoie ce message :
Code:
1 2
| Msg 207, Level 16, State 3, Line 1
Invalid column name 'service_code'. |
Je sais comment faire dans le cas où on utilise des CASE comme expression GROUP BY, mais pour les constantes, je n'ai rien trouvé.
Quelqu'un aurait il une idée ?
Merci à tous. :D