JOIN statement - 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

General SQL Server Forums

New to SQL Server Programming

JOIN statement

Author

Topic

Prabhat33
Starting Member

4 Posts

Posted - 2012-09-19 : 02:37:24

I have a table that has received calls. I have to calculate the total call while avoiding the call < 5 minutes.and, have to charge the call accordingly.The code is like below:
select SUM(Y.recCalls) as RECEIVEDCALLS,
SUM (Y.billedCalls) as CHARGEDCALLS,
(SUM(Y.billedCalls)*100/SUM(recCalls)) as CHARGEDCALLS,
(SUM(Y.billsec) *0.5) as Profit
from Table1 Y
Join (select billedCalls from Table1 group by billedCalls having billedCalls > 5) X
On Y.billedCalls=X.billedCalls

My problem is,the JOIN statement above is also working for RECEIVEDCALLS, and I want to avoid that, is there any way to do this?
Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-19 : 02:50:59

Please give table structure, sample data and wanted result.

Too old to Rock'n'Roll too young to die.

Prabhat33
Starting Member

4 Posts

Posted - 2012-09-19 : 02:54:23

I have this table TABLE1, with loads of numeric data.I want to exclude the numbers less than 5 for the columns, CHARGEDCALLS, PROFIT but while I want the sum to not exclude the numbers less than 5.
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-19 : 03:04:13

Prabhat33, please take a look at http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

KH
[spoiler]Time is always against us[/spoiler]

Prabhat33
Starting Member

4 Posts

Posted - 2012-09-19 : 03:14:34

I have table TABLE1, with numeric datas in column billedCalls (in second) as follows:
4
6
61
22
3
1
120
181

Now, I want sum of all datas under ReceivedCalls column but only those data with more than 5 second in ChargedCalls column.In addition, I want to make a group such that, 5-60 should be listed as 1 CALL, 61-120 as 2 CALL and eventually sum of all total CALL.
Sorry for the confusion above.
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-19 : 03:44:09

quote:
Originally posted by Prabhat33

I have table TABLE1, with numeric datas in column billedCalls (in second) as follows:
4
6
61
22
3
1
120
181

Now, I want sum of all datas under ReceivedCalls column but only those data with more than 5 second in ChargedCalls column.In addition, I want to make a group such that, 5-60 should be listed as 1 CALL, 61-120 as 2 CALL and eventually sum of all total CALL.
Sorry for the confusion above.
Thanks.

you are still not making it easier for us to help you.

quote:
Now, I want sum of all datas under ReceivedCalls column but only those data with more than 5 second in ChargedCalls column

add a where condition to your query

WHERE ChargedCalls  > 5

quote:
In addition, I want to make a group such that, 5-60 should be listed as 1 CALL, 61-120 as 2 CALL and eventually sum of all total CALL.

is it based on ChargedCalls or ReceivedCalls or other column ? i assumed it is ChargedCalls. And you didn't mention what do you want show for 1 CALL, 2 CALL etc


SELECT  
    SUM(CASE WHEN ChargedCalls between 5 and 60   THEN ??? ELSE 0 END) as [1 CALL],
    SUM(CASE WHEN ChargedCalls between 61 and 120 THEN ??? ELSE 0 END) as [2 CALL],
    . . . 
FROM    TABLE1

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources