Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sum from the results of a Select Query

Status
Not open for further replies.

mattm31

Technical User
Mar 9, 2005
79
GB
Here is a select query i have,
Code:
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

Code:
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.
 
Just add an alias after )

E.g.

select MyOneField/myAnotherField from (very complex select here) MyInnnerSelect
 
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
 
Yes - what is your SQL Server version?

I'll write it later, running right now.
 
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...
Code:
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
 
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.
 
Thanks guys i'll run it later and let you know.
 
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
 
It seems to me the Having clause is of no use.
Why the datetime conversion?

Why not...

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top