| Author |
 Topic  |
|
|
djavet
Starting Member
36 Posts |
 Posted - 06/22/2005 : 08:24:27
|
Hello
I've a working (and correct sum) query wich list the hours form each employees in departement with a single table "heures_sap":
SELECT
heures_sap.ctre_emet, sum(heures_sap.heures) as sub_total_heures
FROM
heures_sap
group by
heures_sap.ctre_emet
Results:
ctre_emet sub_total_heures
124600 1572.85
124100 2246.96
124200 1439.27
124900 964.78
124300 682.59
124000 486.25
124400 1360.32
124500 1397.77
I wish to rename the departement "ctre_emet" with this query:
SELECT
succursales, SUM(heures_sap.heures) AS sub_total_heures
FROM
heures_sap,
(SELECT
CASE
WHEN heures_sap.ctre_emet IN (124000) THEN 'Leitung, RC, SA'
WHEN heures_sap.ctre_emet IN (124100) THEN 'KSC'
WHEN heures_sap.ctre_emet IN (124200) THEN 'FM Fribourg'
WHEN heures_sap.ctre_emet IN (124300) THEN 'FM Valais'
WHEN heures_sap.ctre_emet IN (124400) THEN 'FM Vaud' WHEN heures_sap.ctre_emet IN (124500) THEN 'FM Genève'
WHEN heures_sap.ctre_emet IN (124600) THEN 'FM Tessin'
WHEN heures_sap.ctre_emet IN (124900) THEN 'CNP'
ELSE 'Autres succursales'
END AS succursales
FROM heures_sap)t
GROUP BY succursales
Results:
succursales sub_total_heures
FM Fribourg 4730268.14
Leitung, RC, SA 862817.15
KSC 6141227.95
FM Genève 4425744.44
FM Valais 1664729.56
FM Vaud 4141522.32
CNP 2801618.04
FM Tessin 6912687.99
Autres succursales 30452.37
The problem are the sum with the new query! I not my real sum, exemple "FM Tessin" must be 1572.85 and NOT 6912687.99...
I don't understand where is my error.
A lot of thx for yoru help and time.
Regards, Dominique |
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/22/2005 : 08:39:39
|
you've got duplicates and a cross join... try this out:
SELECT
succursales,
sub_total_heures = SUM(heures_sap.heures)
FROM
(SELECT
heures,
succursales = CASE
WHEN heures_sap.ctre_emet IN (124000) THEN 'Leitung, RC, SA'
WHEN heures_sap.ctre_emet IN (124100) THEN 'KSC'
WHEN heures_sap.ctre_emet IN (124200) THEN 'FM Fribourg'
WHEN heures_sap.ctre_emet IN (124300) THEN 'FM Valais'
WHEN heures_sap.ctre_emet IN (124400) THEN 'FM Vaud'
WHEN heures_sap.ctre_emet IN (124500) THEN 'FM Genève'
WHEN heures_sap.ctre_emet IN (124600) THEN 'FM Tessin'
WHEN heures_sap.ctre_emet IN (124900) THEN 'CNP'
ELSE 'Autres succursales' END
FROM heures_sap)t
GROUP BY succursales
Corey
Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."  |
 |
|
|
djavet
Starting Member
36 Posts |
Posted - 06/22/2005 : 08:47:24
|
quote:
Originally posted by Seventhnight
you've got duplicates and a cross join... try this out:
SELECT
succursales,
sub_total_heures = SUM(heures_sap.heures)
FROM
(SELECT
heures,
succursales = CASE
WHEN heures_sap.ctre_emet IN (124000) THEN 'Leitung, RC, SA'
WHEN heures_sap.ctre_emet IN (124100) THEN 'KSC'
WHEN heures_sap.ctre_emet IN (124200) THEN 'FM Fribourg'
WHEN heures_sap.ctre_emet IN (124300) THEN 'FM Valais'
WHEN heures_sap.ctre_emet IN (124400) THEN 'FM Vaud'
WHEN heures_sap.ctre_emet IN (124500) THEN 'FM Genève'
WHEN heures_sap.ctre_emet IN (124600) THEN 'FM Tessin'
WHEN heures_sap.ctre_emet IN (124900) THEN 'CNP'
ELSE 'Autres succursales' END
FROM heures_sap)t
GROUP BY succursales
Thx for your reply, but I receive an error with your query:
The column prefix 'heures_sap' does not match with a table name or alias name used in the query
humm?
Regards, DOm |
|
|
|
djavet
Starting Member
36 Posts |
Posted - 06/22/2005 : 08:53:31
|
I found the error:
Remove "heures_sap." (notice de '.')from:
sub_total_heures = SUM(heures_sap.heures)
to this:
sub_total_heures = SUM(heures)
Why can I not use the table name into a sum()?
Thx for your quick help!
Regards, Dominique
PS: Congratulation for your baby (on the signature), my daughter is 3 and it's the most beautiful event in my life! |
|
|
|
djavet
Starting Member
36 Posts |
Posted - 06/22/2005 : 09:22:11
|
Hello
I've still a little question:
How could I add a Grand Total from my "sub_total_heures" into my query:
SELECT
succursales,
sub_total_heures = SUM(heures)
FROM
(SELECT
heures,
succursales = CASE
WHEN heures_sap.ctre_emet IN (124000) THEN 'Leitung, RC, SA'
WHEN heures_sap.ctre_emet IN (124100) THEN 'CSC'
WHEN heures_sap.ctre_emet IN (124200) THEN 'FM Fribourg'
WHEN heures_sap.ctre_emet IN (124300) THEN 'FM Valais'
WHEN heures_sap.ctre_emet IN (124400) THEN 'FM Vaud'
WHEN heures_sap.ctre_emet IN (124500) THEN 'FM Genève'
WHEN heures_sap.ctre_emet IN (124600) THEN 'FM Tessin'
WHEN heures_sap.ctre_emet IN (124900) THEN 'CNP'
ELSE 'Autres succursales' END
FROM heures_sap
where heures_sap.heures >= 0)t
GROUP BY succursales
ORDER BY succursales ASC
A lot of thx,
Dominique |
Edited by - djavet on 06/22/2005 10:31:36 |
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/22/2005 : 14:02:58
|
quote:
Originally posted by djavet
I found the error:
Remove "heures_sap." (notice de '.')from:
sub_total_heures = SUM(heures_sap.heures)
to this:
sub_total_heures = SUM(heures)
Why can I not use the table name into a sum()?
Thx for your quick help!
Regards, Dominique
PS: Congratulation for your baby (on the signature), my daughter is 3 and it's the most beautiful event in my life!
You can use a table name, but you are using a 'subquery' (correlated query)... so you must use the appropriate alias:
So this would work:
sub_total_heures = SUM(t.heures)
PS. It has definitely be amazing to see my daughter grow (and she's only 7 months so far) 
Corey
Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now." |
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/22/2005 : 14:05:53
|
As for grand total, I would say its best to do in the presentation layer but here's how you get it:
SELECT
succursales,
sub_total_heures = SUM(t.heures),
grand_total_heures = (Select Sum(heures) FROM heures_sap where heures_sap.heures >= 0)
FROM
(SELECT
heures,
succursales = CASE
WHEN heures_sap.ctre_emet IN (124000) THEN 'Leitung, RC, SA'
WHEN heures_sap.ctre_emet IN (124100) THEN 'CSC'
WHEN heures_sap.ctre_emet IN (124200) THEN 'FM Fribourg'
WHEN heures_sap.ctre_emet IN (124300) THEN 'FM Valais'
WHEN heures_sap.ctre_emet IN (124400) THEN 'FM Vaud'
WHEN heures_sap.ctre_emet IN (124500) THEN 'FM Genève'
WHEN heures_sap.ctre_emet IN (124600) THEN 'FM Tessin'
WHEN heures_sap.ctre_emet IN (124900) THEN 'CNP'
ELSE 'Autres succursales' END
FROM heures_sap
where heures_sap.heures >= 0)t
GROUP BY succursales
ORDER BY succursales ASC
Corey
Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now." |
|
|
|
djavet
Starting Member
36 Posts |
Posted - 06/23/2005 : 02:14:06
|
Hello
A lot of thx, but why it's better to do it in the layout presentation?
REgards, Dom |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 06/23/2005 : 02:17:52
|
quote:
Originally posted by djavet
Hello
A lot of thx, but why it's better to do it in the layout presentation?
REgards, Dom
To reduce the work load at server side
Madhivanan
Failing to plan is Planning to fail |
|
|
|
djavet
Starting Member
36 Posts |
Posted - 06/23/2005 : 02:21:00
|
I see now.
This query is for a Intranet application, so I've not this problem.
Good point
Regards, Dom
PS: Thx once again for the help I find here ! |
|
|
| |
Topic |
|
sum() and renaming
