Alias question

The following is not working:
SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
[Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM [Table]
SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
the above example) when I try to use them in later calculations. Is there a
way to do this without actually having to do all the calculations for each
successive column? I've got a lot more calculations to do than just the ones
I'm showing here, so I'd like to limit the amount of SQL code to sift throug
h
if at all possible.Your alternatives are views/derived tables or reusing the entire expression.
So you can have:
SELECT "Total Calls" / "Conversion Rate" AS "Customers",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" AS "Sales",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" * "Profit Margin" AS "Profit"
FROM Table ;
-- or
SELECT Customers,
Customers * Customer_value AS Sales,
Customers * Customer_value * Profit_margin AS profit
FROM (
SELECT "Total Calls" / "Conversion Rate",
"Customer Value", "Profit Margin"
FROM table
) Derived_tbl ( Customers, Customer_value, Profit_margin ) ;
Anith|||Hi,
You can not use alias for this. The approaches are:-
1. As you mentioned use the calculations for each columns
2. Declare variables and use the variables in select statement
Eg:-
Declare @.customers int,
@.Sales int,
@.profit int
SELECT @.Customers = [Total Calls] / [Conversion Rate] , @.Sales= @.Customers
*
[Customer Value] , @.Profit = @.Sales * [Profit Margin] FROM [Table]
Select @.customers,@.sales,@.Profit
Thanks
Hari
SQL Server MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:F7718D7F-2203-44A3-B664-4FCD4E9CFACE@.microsoft.com...
> The following is not working:
> SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
> [Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM
> [Table]
> SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
> the above example) when I try to use them in later calculations. Is there
> a
> way to do this without actually having to do all the calculations for each
> successive column? I've got a lot more calculations to do than just the
> ones
> I'm showing here, so I'd like to limit the amount of SQL code to sift
> through
> if at all possible.
>

Aggregates and subqueries

In the pubs database I need to find all the books whose total sales (qty)
exceed the average total sales
I came up with this query...
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is there a better way to write this?
Hi David
Why are you JOINING with the titles table? There is nothing in that table
you are using. Sales has a title_id. You would only have to JOIN to titles
if you wanted the book title.
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David F" <davef@.nksj.ru> wrote in message
news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
> In the pubs database I need to find all the books whose total sales (qty)
> exceed the average total sales
> I came up with this query...
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> Is there a better way to write this?
>
|||Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>
|||Not quite. Check your GROUP BY:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David F" <davef@.nksj.ru> wrote in message news:OzHtRagGEHA.3816@.TK2MSFTNGP12.phx.gbl...
Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>

Aggregates and subqueries

In the pubs database I need to find all the books whose total sales (qty)
exceed the average total sales
I came up with this query...
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is there a better way to write this?Hi David
Why are you JOINING with the titles table? There is nothing in that table
you are using. Sales has a title_id. You would only have to JOIN to titles
if you wanted the book title.
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David F" <davef@.nksj.ru> wrote in message
news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
> In the pubs database I need to find all the books whose total sales (qty)
> exceed the average total sales
> I came up with this query...
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> Is there a better way to write this?
>|||Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>|||Not quite. Check your GROUP BY:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David F" <davef@.nksj.ru> wrote in message news:OzHtRagGEHA.3816@.TK2MSFTNGP1
2.phx.gbl...
Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>

Aggregate integrity problems

Has anyone else had any problems with cubes showing incorrect aggregates?
For instance, the sales for a Quarter is showing twice the amount of the 3
months showing. As well, there are values associated to products (lowest
level in dimension) that should not be there. When I re-process the cubes,
the issues are eventually resolved but I feel that I cannot trust any numbers
that the cubes show now.
Thanks in advance
Data Integrity starts at the souce and, once validated, is only maintained
through constraints.
The old adage applies here: Garbage In, Garbage Out.
I'd look at the integrity constraints of the primary sources. If those look
intact, I'd attempt to run manual queries to aggregate the data to see if any
anomolies show up. Finally, if all looks well, the next time some
abnormality shows up in the cube, instead of reprocessing, I'd look at the
source first to see if you could manually replicate the numbers.
I have not seen what you are describing except where there were data errors
in the sources. But if you feel that all has been checked out, I would most
certainly open up a PSS case.
Sincerely,
Anthony Thomas
"fastforward" wrote:

> Has anyone else had any problems with cubes showing incorrect aggregates?
> For instance, the sales for a Quarter is showing twice the amount of the 3
> months showing. As well, there are values associated to products (lowest
> level in dimension) that should not be there. When I re-process the cubes,
> the issues are eventually resolved but I feel that I cannot trust any numbers
> that the cubes show now.
> Thanks in advance
|||I agree, Garbage In, Garbage Out. I have experience more than I want to.
In this case however, the data is clean. It has been checked and double
checked. There are no problems with this set of data. Could this have
anything to do with how I have designed my aggregates or writeback issues?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> Data Integrity starts at the souce and, once validated, is only maintained
> through constraints.
> The old adage applies here: Garbage In, Garbage Out.
> I'd look at the integrity constraints of the primary sources. If those look
> intact, I'd attempt to run manual queries to aggregate the data to see if any
> anomolies show up. Finally, if all looks well, the next time some
> abnormality shows up in the cube, instead of reprocessing, I'd look at the
> source first to see if you could manually replicate the numbers.
> I have not seen what you are describing except where there were data errors
> in the sources. But if you feel that all has been checked out, I would most
> certainly open up a PSS case.
> Sincerely,
>
> Anthony Thomas
>
> "fastforward" wrote:
|||Data integrity is always issue number one but if you've checked that out,
then logic comes next. I'd run the aggregate process manually on a few
deminsions with normal T-SQL aggregates. Then, most certainly, I would
consider a more simple design for your cubes to see if the problem clears up.
If so, I'd add complexity, one layer at a time.
If you continue to run into aggregations issues, I would most certainly
consider contacting MS PSS.
Sincerely,
Anthony Thomas
"fastforward" wrote:
[vbcol=seagreen]
> I agree, Garbage In, Garbage Out. I have experience more than I want to.
> In this case however, the data is clean. It has been checked and double
> checked. There are no problems with this set of data. Could this have
> anything to do with how I have designed my aggregates or writeback issues?
> "AnthonyThomas" wrote:

Aggregate integrity problems

Has anyone else had any problems with cubes showing incorrect aggregates?
For instance, the sales for a Quarter is showing twice the amount of the 3
months showing. As well, there are values associated to products (lowest
level in dimension) that should not be there. When I re-process the cubes,
the issues are eventually resolved but I feel that I cannot trust any numbers
that the cubes show now.
Thanks in advanceData Integrity starts at the souce and, once validated, is only maintained
through constraints.
The old adage applies here: Garbage In, Garbage Out.
I'd look at the integrity constraints of the primary sources. If those look
intact, I'd attempt to run manual queries to aggregate the data to see if any
anomolies show up. Finally, if all looks well, the next time some
abnormality shows up in the cube, instead of reprocessing, I'd look at the
source first to see if you could manually replicate the numbers.
I have not seen what you are describing except where there were data errors
in the sources. But if you feel that all has been checked out, I would most
certainly open up a PSS case.
Sincerely,
Anthony Thomas
"fastforward" wrote:
> Has anyone else had any problems with cubes showing incorrect aggregates?
> For instance, the sales for a Quarter is showing twice the amount of the 3
> months showing. As well, there are values associated to products (lowest
> level in dimension) that should not be there. When I re-process the cubes,
> the issues are eventually resolved but I feel that I cannot trust any numbers
> that the cubes show now.
> Thanks in advance|||I agree, Garbage In, Garbage Out. I have experience more than I want to.
In this case however, the data is clean. It has been checked and double
checked. There are no problems with this set of data. Could this have
anything to do with how I have designed my aggregates or writeback issues?
"AnthonyThomas" wrote:
> Data Integrity starts at the souce and, once validated, is only maintained
> through constraints.
> The old adage applies here: Garbage In, Garbage Out.
> I'd look at the integrity constraints of the primary sources. If those look
> intact, I'd attempt to run manual queries to aggregate the data to see if any
> anomolies show up. Finally, if all looks well, the next time some
> abnormality shows up in the cube, instead of reprocessing, I'd look at the
> source first to see if you could manually replicate the numbers.
> I have not seen what you are describing except where there were data errors
> in the sources. But if you feel that all has been checked out, I would most
> certainly open up a PSS case.
> Sincerely,
>
> Anthony Thomas
>
> "fastforward" wrote:
> > Has anyone else had any problems with cubes showing incorrect aggregates?
> > For instance, the sales for a Quarter is showing twice the amount of the 3
> > months showing. As well, there are values associated to products (lowest
> > level in dimension) that should not be there. When I re-process the cubes,
> > the issues are eventually resolved but I feel that I cannot trust any numbers
> > that the cubes show now.
> >
> > Thanks in advance|||Data integrity is always issue number one but if you've checked that out,
then logic comes next. I'd run the aggregate process manually on a few
deminsions with normal T-SQL aggregates. Then, most certainly, I would
consider a more simple design for your cubes to see if the problem clears up.
If so, I'd add complexity, one layer at a time.
If you continue to run into aggregations issues, I would most certainly
consider contacting MS PSS.
Sincerely,
Anthony Thomas
"fastforward" wrote:
> I agree, Garbage In, Garbage Out. I have experience more than I want to.
> In this case however, the data is clean. It has been checked and double
> checked. There are no problems with this set of data. Could this have
> anything to do with how I have designed my aggregates or writeback issues?
> "AnthonyThomas" wrote:
> > Data Integrity starts at the souce and, once validated, is only maintained
> > through constraints.
> >
> > The old adage applies here: Garbage In, Garbage Out.
> >
> > I'd look at the integrity constraints of the primary sources. If those look
> > intact, I'd attempt to run manual queries to aggregate the data to see if any
> > anomolies show up. Finally, if all looks well, the next time some
> > abnormality shows up in the cube, instead of reprocessing, I'd look at the
> > source first to see if you could manually replicate the numbers.
> >
> > I have not seen what you are describing except where there were data errors
> > in the sources. But if you feel that all has been checked out, I would most
> > certainly open up a PSS case.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > "fastforward" wrote:
> >
> > > Has anyone else had any problems with cubes showing incorrect aggregates?
> > > For instance, the sales for a Quarter is showing twice the amount of the 3
> > > months showing. As well, there are values associated to products (lowest
> > > level in dimension) that should not be there. When I re-process the cubes,
> > > the issues are eventually resolved but I feel that I cannot trust any numbers
> > > that the cubes show now.
> > >
> > > Thanks in advance

Aggregate integrity problems

Has anyone else had any problems with cubes showing incorrect aggregates?
For instance, the sales for a Quarter is showing twice the amount of the 3
months showing. As well, there are values associated to products (lowest
level in dimension) that should not be there. When I re-process the cubes,
the issues are eventually resolved but I feel that I cannot trust any number
s
that the cubes show now.
Thanks in advanceData Integrity starts at the souce and, once validated, is only maintained
through constraints.
The old adage applies here: Garbage In, Garbage Out.
I'd look at the integrity constraints of the primary sources. If those look
intact, I'd attempt to run manual queries to aggregate the data to see if an
y
anomolies show up. Finally, if all looks well, the next time some
abnormality shows up in the cube, instead of reprocessing, I'd look at the
source first to see if you could manually replicate the numbers.
I have not seen what you are describing except where there were data errors
in the sources. But if you feel that all has been checked out, I would most
certainly open up a PSS case.
Sincerely,
Anthony Thomas
"fastforward" wrote:

> Has anyone else had any problems with cubes showing incorrect aggregates?
> For instance, the sales for a Quarter is showing twice the amount of the 3
> months showing. As well, there are values associated to products (lowest
> level in dimension) that should not be there. When I re-process the cubes
,
> the issues are eventually resolved but I feel that I cannot trust any numb
ers
> that the cubes show now.
> Thanks in advance|||I agree, Garbage In, Garbage Out. I have experience more than I want to.
In this case however, the data is clean. It has been checked and double
checked. There are no problems with this set of data. Could this have
anything to do with how I have designed my aggregates or writeback issues?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> Data Integrity starts at the souce and, once validated, is only maintained
> through constraints.
> The old adage applies here: Garbage In, Garbage Out.
> I'd look at the integrity constraints of the primary sources. If those lo
ok
> intact, I'd attempt to run manual queries to aggregate the data to see if
any
> anomolies show up. Finally, if all looks well, the next time some
> abnormality shows up in the cube, instead of reprocessing, I'd look at the
> source first to see if you could manually replicate the numbers.
> I have not seen what you are describing except where there were data error
s
> in the sources. But if you feel that all has been checked out, I would mo
st
> certainly open up a PSS case.
> Sincerely,
>
> Anthony Thomas
>
> "fastforward" wrote:
>|||Data integrity is always issue number one but if you've checked that out,
then logic comes next. I'd run the aggregate process manually on a few
deminsions with normal T-SQL aggregates. Then, most certainly, I would
consider a more simple design for your cubes to see if the problem clears up
.
If so, I'd add complexity, one layer at a time.
If you continue to run into aggregations issues, I would most certainly
consider contacting MS PSS.
Sincerely,
Anthony Thomas
"fastforward" wrote:
[vbcol=seagreen]
> I agree, Garbage In, Garbage Out. I have experience more than I want to.
> In this case however, the data is clean. It has been checked and double
> checked. There are no problems with this set of data. Could this have
> anything to do with how I have designed my aggregates or writeback issues?
> "AnthonyThomas" wrote:
>

Aggregate functions in multiple tables

Hi, need help in this statement here. I have three tables here, i.e. Sales, SalesItem, & SalesPmt. I want to display a grid that shows the Total Bill and Total Payment amounts.

My try is like this: SELECT SalesNo, SUM(Price*Qty) AS TotalBill, SUM(Payment) AS TotalPayment FROM ... GROUP BY...

No syntax error or whatever found, but the result of the total amounts is incorrect.

Say the data of the respective table below:

SalesItem

NoQtyPrice115.002212.00343.50

SalesPayment

NoAmount110.0025.00

But the result I get from the above query is:

TotalBillTotalPayment86.0045.00

Total Bill should be 43.00 and Total Payment should be 15.00.

Apparently the problem is due to the fact that I and querying on multiple tables. The correct total payment amount was multiplied by the number of rows of sales items (15.00 x 3), while the correct total bill amount was multiplied by the number of rows of sale payments (43.00 x 2).

So, what is the better way of writing this query?

Use table name or table alias for each table and show the tablr (or alias) in front of column names.

Your query will look like this:

SELECT a.SalesNo, SUM(a.Price*a.Qty) AS TotalBill, SUM(b.Payment) AS TotalPayment FROM SalesItem AS a INNER JOIN SalesPayment AS b ON a.SalesNo= b.SalesNo GROUP BY a.SalesNo

|||

All the column names are unique in these three tables except the foreign key, SalesNo.

There is no difference happening here... :(

I guess I have to add two more columns in the Sales table and programmatically insert/update the TotalBill & TotalPayment just to have the grid with these two information available.

Or should I, the other way round, programatically calculate them when retriving the resultset for the grid?

Which one is the better way in the sense of processing performance? I think there sure is a difference between these two methods if you are retirving thousands of records...

I bet the first method is better, you think?

|||Could you add the table which is missing from your first post and includes all your key columns?|||

Sorry for late reply, please see the following for tables and key columns:

Table - Sales
SalesNo PK

Table - SalesItem
SalesItemNo PK
SalesNo FK
ItemCode
Qty
Price

Table - SalesPayment
SalesPaymentNo PK
SalesNo FK
Amount

|||

You should use sam data along with your table too.

Here is a query to get 43 and 15. no group by.

SELECT SUM(a.Price*a.Qty) AS TotalBill, SUM(b.Amount) AS TotalPayment
FROM SalesItem AS a LEFT JOIN SalesPayment AS b ON
a.SalesNo= b.SalesNo