Sum () query

[crusher88]

Is there any way to execute this as one query:

SELECT `cusid` AS Kund, SUM(select `chargeabletime` where `chargeratetype` = "N") AS Normal, SUM(select `chargeabletime` where `chargeratetype` = "O") AS Ovrig, SUM(select `chargeabletime` where `chargeratetype` = "G") AS Garanti, COUNT(*) As Total

I know that the above query is not valid, but it might help to understand what I want to achieve.

My table looks something like this:

cusid	chargeabletime	chargeratetype
174000		1		N
370047		2		G
6806000		3		N
6806000		2		O
6806000		3		N
6806000		2		N
208700		2		N
174000		1		N

Anyone?

[LordShryku]

Not sure I completely follow your pseudo-sql, but here's a stab at it

  SELECT cusid,
         chargeratetype,
         sum(chargeabletime) AS t_time
    FROM <table>
   WHERE chargeratetype IN ('N','O','G')
GROUP BY cusid, chargeratetype



# Should produce results like this

 cusid   chargeratetype  t_time
-------  --------------  ------
6806000  N               8
6806000	 O               2

[crusher88]

Thanks for your reply!

What I wanted was to display the results in a table grouped by cusid like this:

cusid     Normal   Ovrig   Garanti
-----     ------   -----   ------- 
6806000     6        1       2
174000	    8        0       4
182000      6        1       1

[crusher88]

SELECT `cusid` AS Kund, SUM( 
IF (
chargeratetype = "N", 1, 0
) ) AS Normal, SUM( 
IF (
chargeratetype = "O", 1, 0
) ) AS Ovrig, SUM( 
IF (
chargeratetype = "G", 1, 0
) ) AS Garanti, COUNT( * ) AS Total
FROM jobshistory
WHERE 1 
GROUP BY `cusid` 
ORDER BY `cusid`

The above query is going to select data from this table:

cusid	chargeabletime	chargeratetype
174000		1		N
370047		2		G
6806000		3		N
6806000		2		O
6806000		3		N
6806000		2		N
208700		2		N
174000		1		N

This gives the result something like this (not the real values):

Kund	Normal	Ovrig	Garanti	Total
174000 	5 	0 	2 	7 
180200 	3 	1 	1 	5 
184700 	1 	0 	1 	2 

My question is: Why does the "Normal" column include all other columns? It doesnt only list results marked as "N".

[tsinka]

Hi,

does it work if you use this query ?

SELECT `cusid` AS Kund, SUM(  

IF ( 
chargeratetype = "N", chargeabletime, 0 
) ) AS Normal, SUM(  

IF ( 
chargeratetype = "O", chargeabletime, 0 
) ) AS Ovrig, SUM(  

IF ( 
chargeratetype = "G", chargeabletime, 0 
) ) AS Garanti, COUNT( * ) AS Total 
FROM jobshistory 
WHERE 1  

GROUP BY `cusid`  

ORDER BY `cusid`

Thomas

[crusher88]

Actually I tested the above query before reading this, and it worked as expected.

Thanks a lot anyway!