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!

Register Log in
  • Congratulations SkipVought 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

mattm31

Technical User
Mar 9, 2005
79
0
0
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.
 
Sort by date Sort by votes
Just add an alias after )

E.g.

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

I'll write it later, running right now.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Thanks guys i'll run it later and let you know.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
155
Andrzejek
Andrzejek
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
90
grnzbra
grnzbra
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
169
Chris Miller
Chris Miller
bharons
  • Question
Pick up value from combobox
Replies
0
Views
140
bharons
bharons
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
76
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top