sum() and renaming - 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 2000 Forums

SQL Server Development (2000)

sum() and renaming

Author

Topic

djavet
Starting Member

36 Posts

Posted - 2005-06-22 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-22 : 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 - 2005-06-22 : 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 - 2005-06-22 : 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 - 2005-06-22 : 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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-22 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-22 : 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 - 2005-06-23 : 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

22864 Posts

Posted - 2005-06-23 : 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 - 2005-06-23 : 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 !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources