• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Divide by additional field from another database

Need to divide the last set byt PerPupilSpending

SELECT
(( SELECT SUM(S2C_TTL) FROM njsurvey.dbo.AES_ES_2)
  +  ( SELECT SUM(S2C_TTL) FROM njsurvey.dbo.AES_HS_2))
  / (Select SUM(ENROLL_TTL) from njsurvey.dbo.AES_CONTACT_SCHOOL)AS PerPupilSpending
/(select SUM(CL_TOT) from reportcards.dbo.finance WHERE YEAR = '0405') AS TotalCost
0
vonfranzken
Asked:
vonfranzken
  • 4
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should do this in 2 steps:

declare @x decimal (20,4)
declare @p decimal (20,4)
declare @t decimal (20,4)

SET NOCOUNT ON
SELECT @x =
(
      ( SELECT SUM(S2C_TTL) FROM njsurvey.dbo.AES_ES_2)
  +  ( SELECT SUM(S2C_TTL) FROM njsurvey.dbo.AES_HS_2)
)

select @p = SUM(ENROLL_TTL) from njsurvey.dbo.AES_CONTACT_SCHOOL
select @t = SUM(CL_TOT) from reportcards.dbo.finance WHERE YEAR = '0405'

select
  @x / @p AS PerPupilSpending
  @x / @t AS TotalCost
0
 
vonfranzkenAuthor Commented:
Can I run this in query manager?

Isnt there a way to do it using the beggining point we had worked on together from the query above?

Would rather have it that way as I am finding it to be more confusing now.

Also getting;
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '@x'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see I was missing a comma in the last select statement:

declare @x decimal (20,4)
declare @p decimal (20,4)
declare @t decimal (20,4)

SET NOCOUNT ON
SELECT @x =
(
      ( SELECT SUM(S2C_TTL) FROM njsurvey.dbo.AES_ES_2)
  +  ( SELECT SUM(S2C_TTL) FROM njsurvey.dbo.AES_HS_2)
)

select @p = SUM(ENROLL_TTL) from njsurvey.dbo.AES_CONTACT_SCHOOL
select @t = SUM(CL_TOT) from reportcards.dbo.finance WHERE YEAR = '0405'

select  @x / @p AS PerPupilSpending
  ,  @x / @t AS TotalCost


>Can I run this in query manager?
Yes

>Isnt there a way to do it using the beggining point we had worked on together from the query above?
you could, but the way I show you is 1) easier to maintain 2) easier to read (IMHO), 3) better scalable
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
vonfranzkenAuthor Commented:
Very cool I see what your saying now if I wanted to lets say multiply by the result of another select
I merely declare the variable define its type select @m = lets say and do the math at the end,
in this example what if I wanted to multiply the total cost by my @m, what would it look like

@x/@t*@m=NewResult ???

Lastly how could I round off the results?

The show as

PerPupilSpending = 1.753880849004775018
Total Cost = .244213803587716631
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this looks like you declare the table fields and/or the variables as FLOAT, which are not recommended at all, you should use decimal instead.

yes:
select (@x/@t)*@m As NewResult

to do the rounding explicitely:
select cast ( (@x/@t)*@m  as decimal(20,2)) As NewResult


0
 
vonfranzkenAuthor Commented:
Cool do you need the paranthesis in:

select (@x/@t)*@m As NewResult
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, because / is evaluated before *, but I prefer to put them to make it clear for readers.
See, with the ( ) it is CLEAR to everyone that you want to make the / first, without the / you have to ask yourself the question or test or read the documentation.
0
 
vonfranzkenAuthor Commented:
Thanks so much because I have learned so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now