Sum from the results of a Select Query

[mattm31]

Here is a select query i have,

SELECT Tame_cust.Rep_Name, 
sum (Case Tame_cust.hlp_benefits When 'No' Then 1 Else 0 End) As [ATP Leads],
sum (Case Tame_cust.hlp_benefits 
	When 'Child Tax Credit (less than £14200pa)' Then 1
	When 'ATT Allowance' Then 1
	When 'Disability living Allowance' Then 1
	When 'State Pension Credit' Then 1
	When 'Income based JSA' Then 1
	When 'Council Tax' Then 1
	When 'Housing' Then 1
	When 'Income Support' Then 1	
	When 'War Disablement Pension' Then 1
	When 'Disablement Allowance Benefit' Then 1
	When 'Working Tax Credit (Less than £14200pa)' Then 1
	When 'Industrial Injurys' Then 1
	When 'Over 70' Then 1
	Else 0 
	End) As [PG Leads],
sum (Case Tame_cust.tsr When 'HOT' Then 1 Else 0 End) As [HOT Leads],
sum (Case When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'L' Then 1
	  When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'C' Then 1 
          When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'D' Then 2
	  Else 0 End) As [ATP Sales],
sum (Case When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'L' Then 1
	  When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'C' Then 1 
          When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'D' Then 2
	  Else 0 End) As [PG Sales],
sum (Case When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'L' Then 1
	  When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'C' Then 1 
          When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'D' Then 2
	  Else 0 End) As [HL Sales]


FROM Tame_cust
Where tame_cust.Status_code <>'PB'
GROUP BY Tame_cust.Rep_Name, Tame_cust.appt_date
HAVING CONVERT(DATETIME, tame_cust.appt_date, 103) = CONVERT(nvarchar, '07/07/2009', 103)

What i would like to do is sum the results [ATP Sales]\[ATP Leads] * 100

I thought i could just do

SELECT sum([ATP Sales]\[ATP Leads]*100) AS Ratio
From (

SELECT Tame_cust.Rep_Name, 
sum (Case Tame_cust.hlp_benefits When 'No' Then 1 Else 0 End) As [ATP Leads],
sum (Case Tame_cust.hlp_benefits 
	When 'Child Tax Credit (less than £14200pa)' Then 1
	When 'ATT Allowance' Then 1
	When 'Disability living Allowance' Then 1
	When 'State Pension Credit' Then 1
	When 'Income based JSA' Then 1
	When 'Council Tax' Then 1
	When 'Housing' Then 1
	When 'Income Support' Then 1	
	When 'War Disablement Pension' Then 1
	When 'Disablement Allowance Benefit' Then 1
	When 'Working Tax Credit (Less than £14200pa)' Then 1
	When 'Industrial Injurys' Then 1
	When 'Over 70' Then 1
	Else 0 
	End) As [PG Leads],
sum (Case Tame_cust.tsr When 'HOT' Then 1 Else 0 End) As [HOT Leads],
sum (Case When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'L' Then 1
	  When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'C' Then 1 
          When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'D' Then 2
	  Else 0 End) As [ATP Sales],
sum (Case When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'L' Then 1
	  When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'C' Then 1 
          When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'D' Then 2
	  Else 0 End) As [PG Sales],
sum (Case When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'L' Then 1
	  When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'C' Then 1 
          When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'D' Then 2
	  Else 0 End) As [HL Sales]


FROM Tame_cust
Where tame_cust.Status_code <>'PB'
GROUP BY Tame_cust.Rep_Name, Tame_cust.appt_date
HAVING CONVERT(DATETIME, tame_cust.appt_date, 103) = CONVERT(nvarchar, '07/07/2009', 103)
)

But that just errors on the last line.

Help anybody please.

 

[markros]

Just add an alias after )

E.g.

select MyOneField/myAnotherField from (very complex select here) MyInnnerSelect

 

[mattm31]

Hi Mark

That worked thanks, but not as i was expecting,
my orignal query result looked like.

Rep_name ATP Leads ATP Sales
Mr E Mann 4 4

What i was trying to achive is a field on the end called Ratio with the result of the extra query.

Is that possiable ?

Thanks

Matt

 

[markros]

Yes - what is your SQL Server version?

I'll write it later, running right now.

 

[mattm31]

LOL thks Mark its 2000

 

[gmmastros]

I think in this case, you don't need to use a derived table.

Ex:

[tt][blue]
Select Col1,
Sum(ColX) As Sales,
Sum(ColY) As Leads,
Sum(ColX) / Sum(ColY) As [Sales Per Lead]
From SomeTable
[/blue][/tt]

Try this...

SELECT Tame_cust.Rep_Name,
sum (Case Tame_cust.hlp_benefits When 'No' Then 1 Else 0 End) As [ATP Leads],
sum (Case Tame_cust.hlp_benefits
    When 'Child Tax Credit (less than £14200pa)' Then 1
    When 'ATT Allowance' Then 1
    When 'Disability living Allowance' Then 1
    When 'State Pension Credit' Then 1
    When 'Income based JSA' Then 1
    When 'Council Tax' Then 1
    When 'Housing' Then 1
    When 'Income Support' Then 1    
    When 'War Disablement Pension' Then 1
    When 'Disablement Allowance Benefit' Then 1
    When 'Working Tax Credit (Less than £14200pa)' Then 1
    When 'Industrial Injurys' Then 1
    When 'Over 70' Then 1
    Else 0
    End) As [PG Leads],
sum (Case Tame_cust.tsr When 'HOT' Then 1 Else 0 End) As [HOT Leads],
sum (Case When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'L' Then 1
      When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'C' Then 1
          When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'D' Then 2
      Else 0 End) As [ATP Sales],
sum (Case When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'L' Then 1
      When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'C' Then 1
          When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'D' Then 2
      Else 0 End) As [PG Sales],
sum (Case When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'L' Then 1
      When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'C' Then 1
          When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'D' Then 2
      Else 0 End) As [HL Sales][!],[/!]


[blue][b]sum (Case When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'L' Then 1
      When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'C' Then 1
          When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'D' Then 2
      Else 0 End)[/b][/blue] / [green][b]sum (Case Tame_cust.hlp_benefits When 'No' Then 1 Else 0 End)[/b][/green] As [ATP Sales Per Lead]

FROM Tame_cust
Where tame_cust.Status_code <>'PB'
GROUP BY Tame_cust.Rep_Name, Tame_cust.appt_date
HAVING CONVERT(DATETIME, tame_cust.appt_date, 103) = CONVERT(nvarchar, '07/07/2009', 103)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, looks like George's solution should work in your case.

I was thinking originally we may want to use CTE (derived table in 2000) with the select, then

select *, OneField / AnotherField as Ratio from Cte, but I guess doing it straight should be the same.

Though out of curiosity I would appreciate if you can compare speed between both approaches.

 

[mattm31]

Thanks guys i'll run it later and let you know.

 

[r937]

somebody please fix that horrible HAVING clause

first, fix the condition so that it's sargable (by removing the function from the column), and then please get it the heck outta the HAVING clause

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[WoodyGuthrie]

It seems to me the Having clause is of no use.
Why the datetime conversion?

Why not...

Where tame_cust.Status_code <>'PB'
and Tame_cust.appt_date = '2009-07-07'
GROUP BY Tame_cust.Rep_Name, Tame_cust.appt_date

Woody