SQL Query Help - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query Help

Author

Topic

vk18
Posting Yak Master

146 Posts

Posted - 2006-12-20 : 12:09:39

I have a query that i need to calculate the Sum of tblspot.rateActual in another Column. Can you help me with this
Thx

SELECT tblNetworks.description AS NETWORK,tblSpot.rateActual as ACTUALRATE, tblAdvertiser.description AS ADVERTISER,
tblagency.description as AGENCY,tblInvoice.invoiceNumber AS INVOICENUMBER FROM tblContract INNER JOIN tblSpot
ON tblContract.pkid = tblSpot.fkContract INNER JOIN tblAdvertiser ON tblContract.fkAdvertiser = tblAdvertiser.pkid
INNER JOIN tblInvoice ON tblSpot.fkInvoiceNumber = tblInvoice.invoiceNumber INNER JOIN tblNetworks
ON tblSpot.fkNetwork = tblNetworks.pkid INNER JOIN tblAgency ON tblSpot.fkAgency = tblAgency.pkid
WHERE (tblSpot.dateActual BETWEEN '" & Me.txtstartdate.Text & "' AND '" & Me.txtenddate.Text & "')
AND (tblSpot.fkContractType = 'unwired') and tbladvertiser.description = '" & Me.ddadvertiser.SelectedValue & "'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 12:52:07

Is this an MS ACCESS query?

Peter Larsson
Helsingborg, Sweden

vk18
Posting Yak Master

146 Posts

Posted - 2006-12-20 : 12:52:57

quote:
Originally posted by Peso

Is this an MS ACCESS query?

Peter Larsson
Helsingborg, Sweden

No, It is Sql Server2000
Thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 12:58:17

Really?
Where does the "BETWEEN '" & Me.txtStartDate.Text & "' AND..." part come from?

Either it is VB 6.0 due to the concatenating operators, or MS ACCESS VBA due to the Me keyword.

Peter Larsson
Helsingborg, Sweden

vk18
Posting Yak Master

146 Posts

Posted - 2006-12-20 : 13:16:56

quote:
Originally posted by Peso

Really?
Where does the "BETWEEN '" & Me.txtStartDate.Text & "' AND..." part come from?

Either it is VB 6.0 due to the concatenating operators, or MS ACCESS VBA due to the Me keyword.

Peter Larsson
Helsingborg, Sweden

It is in .NET

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 13:23:45

Either use a correlated subquery, or put the whole query in GROUP BY.

Peter Larsson
Helsingborg, Sweden

vk18
Posting Yak Master

146 Posts

Posted - 2006-12-20 : 13:27:58

quote:
Originally posted by Peso

Either use a correlated subquery, or put the whole query in GROUP BY.

Peter Larsson
Helsingborg, Sweden

when i tried group by it is giving by group not every thing. is there any way to get everything. Can you show me how
Thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 14:27:29

What does the SUM of rates mean to you?

SELECT tblNetworks.description AS NETWORK,SUM(tblSpot.rateActual) as ACTUALRATE, tblAdvertiser.description AS ADVERTISER,
tblagency.description as AGENCY,tblInvoice.invoiceNumber AS INVOICENUMBER FROM tblContract INNER JOIN tblSpot
ON tblContract.pkid = tblSpot.fkContract INNER JOIN tblAdvertiser ON tblContract.fkAdvertiser = tblAdvertiser.pkid
INNER JOIN tblInvoice ON tblSpot.fkInvoiceNumber = tblInvoice.invoiceNumber INNER JOIN tblNetworks
ON tblSpot.fkNetwork = tblNetworks.pkid INNER JOIN tblAgency ON tblSpot.fkAgency = tblAgency.pkid
WHERE (tblSpot.dateActual BETWEEN '" & Me.txtstartdate.Text & "' AND '" & Me.txtenddate.Text & "')
AND (tblSpot.fkContractType = 'unwired') and tbladvertiser.description = '" & Me.ddadvertiser.SelectedValue & "' GROUP BY tblNetworks.description, tblAdvertiser.description, tblagency.description, tblInvoice.invoiceNumber

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-12-20 : 14:28:38

it looks like a query being built in the front end, and is going to get sent to the back end. Should really be a stored proc.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 14:36:46

Then we have to explain all about parameters and why that is better than dynamic SQL and the possibility of injections.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-20 : 18:15:07

please see:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

for why you should NEVER concatenate sql like this...always use parameters .

- Jeff

vk18
Posting Yak Master

146 Posts

Posted - 2006-12-20 : 18:28:25

quote:
Originally posted by Peso

What does the SUM of rates mean to you?

SELECT tblNetworks.description AS NETWORK,SUM(tblSpot.rateActual) as ACTUALRATE, tblAdvertiser.description AS ADVERTISER,
tblagency.description as AGENCY,tblInvoice.invoiceNumber AS INVOICENUMBER FROM tblContract INNER JOIN tblSpot
ON tblContract.pkid = tblSpot.fkContract INNER JOIN tblAdvertiser ON tblContract.fkAdvertiser = tblAdvertiser.pkid
INNER JOIN tblInvoice ON tblSpot.fkInvoiceNumber = tblInvoice.invoiceNumber INNER JOIN tblNetworks
ON tblSpot.fkNetwork = tblNetworks.pkid INNER JOIN tblAgency ON tblSpot.fkAgency = tblAgency.pkid
WHERE (tblSpot.dateActual BETWEEN '" & Me.txtstartdate.Text & "' AND '" & Me.txtenddate.Text & "')
AND (tblSpot.fkContractType = 'unwired') and tbladvertiser.description = '" & Me.ddadvertiser.SelectedValue & "' GROUP BY tblNetworks.description, tblAdvertiser.description, tblagency.description, tblInvoice.invoiceNumber

Peter Larsson
Helsingborg, Sweden

Hi Peso,

I Know about this group by. I don't want to group by rather i want to sum all the rates and put it in another column and the tblspot.rateactual should remain
Thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 00:34:05

The SUM of rates deos not mean anything.

SELECT tblNetworks.description AS NETWORK,tblSpot.rateActual as ACTUALRATE, 
(select sum(<YourRateColumnNameHere> from <YourRateTableNameHere> as x WHERE x.<somecol> = <outer column name here>.<other column name here>)),
tblAdvertiser.description AS ADVERTISER,
tblagency.description as AGENCY,tblInvoice.invoiceNumber AS INVOICENUMBER FROM tblContract INNER JOIN tblSpot
ON tblContract.pkid = tblSpot.fkContract INNER JOIN tblAdvertiser ON tblContract.fkAdvertiser = tblAdvertiser.pkid
INNER JOIN tblInvoice ON tblSpot.fkInvoiceNumber = tblInvoice.invoiceNumber INNER JOIN tblNetworks 
ON tblSpot.fkNetwork = tblNetworks.pkid INNER JOIN tblAgency ON tblSpot.fkAgency = tblAgency.pkid 
WHERE (tblSpot.dateActual BETWEEN '" & Me.txtstartdate.Text & "' AND '" & Me.txtenddate.Text & "')
AND (tblSpot.fkContractType = 'unwired') and tbladvertiser.description = '" & Me.ddadvertiser.SelectedValue & "'

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources