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.
>

Aggregation problem in Report Designer

Hi!!! Please help me.

I have the following table structure.

-A (name)

--B (name)

--C (name, total)

For example.

A { Tom, Sam John }

B {Mazda, Audi, Ford }

C: { (Monitor, 100), (Telephone, 230), (Mouse, 370)}

The corresponding sql select:

select * from A left join B left join C

Retrive obvios result:

And now I have desing report with the following structure:

1) Create list element (A_List) and use detail group to grouping data by A.name in it.

2) Then I use 2 another lists and placed it in A.

Now I want get the textbox = Sum(C.total) in the A_List area. It is obvious that ealier represented sql select make cartesian product (AxBxC) of A,B,C tables. And now I have multiple record for single row for each row in each table. For example I have three equals records for totals.

And I could't use aggregation function in Sum. Anobody know how this problem solves?

Hi,

I'm not sure I'll be answering exactly what you need, but I'll take the risk ;-)

I will try using a RunningTotal, checking for the condition of change, or, in the worst case creating a function in Report code to perform the custom sum.

You'll find more info in Books On Line.

HTH

Jordi Rambla

Solid Quality Learning

Aggregation problem

Hi

We have a fact table contains 5 dimension keys and one measure.The measure is having value as 1for all records and it will always be 1.Total records in this fact table are 2316.

we processed cube successfully.

Problem : While viewing data in cube browser the measure count is aggregating.We want all the records for the measure is 1. But its showing aggregate values as 1, 2 6..

Thanks

karumuru

Try connecting from Excel or some other tools you have, to the cube.

Also, If you are using the default measure " Fact Count", it will aggregate, as it is a Count.

You can specify another measure and fill it with value 1 (this is just for testing, this is not a good practice), and specify the aggregateFunction property of the measure to DistinctCount.

(I hope you are aware of the Factless fact table concept)

Regards,

Jiju

|||

Jiju

yes, Our one measure is a factless fact and had value 1. This measure aggregation property we set to distinct count and count tried all. but its aggregating and we could not get 2316 rows

Thanks

Sridhar K

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 Total Acreage and Group By for Mail Merge

I was helped on an earlier question to complete my mail merge with the following code:
select YourTable.*
from YourTable
inner join --DistinctNames
(select Max(PrimaryKey) as PrimaryKey
from YourTable
group by FirstName,
LastName) DistinctNames
on YourTable.PrimaryKey = DistinctNames.PrimaryKey
Basically this code queries my mailing list and ensures that i do not send mutiple letters to one person at the same address who might be in the batabase more than once. However, the reason they are in there more than once is that they might own additional properties. Anyway, I have a column that includes their acreage for each property in each record and I would like to add those up for each person during my query. Thought anyone? Thanks!select YourTable.*, DistinctNames.total_acreage
from YourTable
inner join --DistinctNames
(select Max(PrimaryKey) as PrimaryKey, sum(acreage) total_acreage
from YourTable
group by FirstName,
LastName) DistinctNames
on YourTable.PrimaryKey = DistinctNames.PrimaryKey|||Thanks, the query seems to run withour error, however, the "total acreage" field is simply populated with one of their acreage values, not their total. It looks like the sumation is occurring after the new table is created, which would provide an incorrect result. Any other thoughts?|||Nevermind, it worked flawlessly. Thanks a lot!

aggregate functions vs. non-numeric data

RS doesn't like this expression that's supposed to total up all the debit values in my ledger:

=RunningValue( iif(Fields!Amount.Value < 1, 0, Fields!Amount.Value), Sum, Nothing)

I'm getting this error :

The value expression for the textbox ‘APTotal’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

I take issue with its saying my data is not numeric. The matching field in the database is of type money.

Any thoughts on what i'm doing wrong?

Ian Pert
CMS Software, Business Integenct UnitOh, got it. Problem wasn't my Fields!Amount.Value, but simply that 0 wasn't the same datatype. Replaced 0 with Nothing, like so

=RunningValue( iif(Fields!Amount.Value < 1, Nothing, Fields!Amount.Value), Sum, Nothing)

And it worked like a charm.

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

aggregate function

The following statement fail to generate my expected result:
select A.part_id, sum( (B.total + sum(C.amount)) * D.rate)
from A, B, C , D
where B.part_id = A.part_id and C.line_id = B.line_id and convert(char(6),
B.date, 112) = D.code
It generates the error of "Cannot perform an aggregate function on an
expression containing an aggregate or a subquery.".
Could anyone please give me a hand?
Thanks in advance.
SC
----
DDL:
create Table A
( part_id char(1) primary key,
description varchar(1),
)
create Table B
(
part_id char(1),
date datetime,
line_id int,
total numeric(10,2),
primary key (part_id, date)
)
create Table C
(
line_id int,
seq int,
amount numeric(10,2)
primary key (line_id, seq )
)
create Table D
(
code char(6) primary key,
rate numeric(10,2)
)
DML:
insert into A values ( 'A', 'A' )
insert into A values ( 'B', 'B' )
insert into A values ( 'C', 'C' )
insert into B values ( 'A', '2006/01/01', 1, 10)
insert into B values ( 'A', '2006/02/01', 2, 5)
insert into B values ( 'B', '2006/01/01',3, 12)
insert into B values ( 'B', '2006/01/03',4, 10)
insert into B values ( 'B', '2006/02/01',5, 2)
insert into C values ( 1, 1, 3)
insert into C values ( 1, 2, 4)
insert into C values ( 2, 1, 5)
insert into C values ( 3, 1, -5)
insert into C values ( 3, 2, 2)
insert into D values ('200601', 1.1)
insert into D values ('200602', 1.5)
Expect result:
A 33.7
B 23.9I didn't spend time to completely work this out, but it should get you movin
g in the right direction. (The A result is what you desired, but the B resul
t is not...)
Sum Table C as a derived table (named 'C2') and THEN JOIN on it.
SELECT
A.Part_ID
, sum(( B.Total + C2.Amount ) * D.Rate )
FROM A
JOIN B
ON A.Part_ID = B.Part_ID
JOIN ( SELECT
Line_ID
, sum( Amount ) AS 'Amount'
FROM C
GROUP BY Line_ID
) C2
ON C2.Line_ID = B.Line_ID
JOIN D
ON convert( char(6), B.[Date], 112) = D.Code
GROUP BY A.Part_ID
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Squirrel" <xsquirrelx@.hotmail.com> wrote in message news:OwO5qWnlGHA.4212@.TK2MSFTNGP03.phx
.gbl...
> The following statement fail to generate my expected result:
> select A.part_id, sum( (B.total + sum(C.amount)) * D.rate)
> from A, B, C , D
> where B.part_id = A.part_id and C.line_id = B.line_id and convert(char(6),
> B.date, 112) = D.code
>
> It generates the error of "Cannot perform an aggregate function on an
> expression containing an aggregate or a subquery.".
>
> Could anyone please give me a hand?
>
> Thanks in advance.
>
> SC
> ----
> DDL:
> create Table A
> ( part_id char(1) primary key,
> description varchar(1),
> )
> create Table B
> (
> part_id char(1),
> date datetime,
> line_id int,
> total numeric(10,2),
> primary key (part_id, date)
> )
> create Table C
> (
> line_id int,
> seq int,
> amount numeric(10,2)
> primary key (line_id, seq )
> )
> create Table D
> (
> code char(6) primary key,
> rate numeric(10,2)
> )
>
> DML:
> insert into A values ( 'A', 'A' )
> insert into A values ( 'B', 'B' )
> insert into A values ( 'C', 'C' )
> insert into B values ( 'A', '2006/01/01', 1, 10)
> insert into B values ( 'A', '2006/02/01', 2, 5)
> insert into B values ( 'B', '2006/01/01',3, 12)
> insert into B values ( 'B', '2006/01/03',4, 10)
> insert into B values ( 'B', '2006/02/01',5, 2)
> insert into C values ( 1, 1, 3)
> insert into C values ( 1, 2, 4)
> insert into C values ( 2, 1, 5)
> insert into C values ( 3, 1, -5)
> insert into C values ( 3, 2, 2)
> insert into D values ('200601', 1.1)
> insert into D values ('200602', 1.5)
>
> Expect result:
> A 33.7
> B 23.9
>
>|||Hello, Squirrel
The following query returns the expected results:
SELECT Y.part_id, SUM(Y.AnotherSum*D.rate) as TheSum
FROM (
SELECT X.part_id, X.code, SUM(X.TotalPlusAmount) as AnotherSum
FROM (
SELECT B.part_id, CONVERT(char(6),B.date,112) AS code,
B.total+ISNULL((
SELECT SUM(C.amount)
FROM C WHERE B.line_id=C.line_id
),0) as TotalPlusAmount
FROM B
) X GROUP BY X.part_id, X.code
) Y INNER JOIN D ON Y.code = D.code
GROUP BY Y.part_id
Razvan|||Thanks, Razvan.
Frankly, your SQL statement is complicated to me. would you kindly explain
it to me?
Thanks again.
SC
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1151130774.295568.163480@.m73g2000cwd.googlegroups.com...
> Hello, Squirrel
> The following query returns the expected results:
> SELECT Y.part_id, SUM(Y.AnotherSum*D.rate) as TheSum
> FROM (
> SELECT X.part_id, X.code, SUM(X.TotalPlusAmount) as AnotherSum
> FROM (
> SELECT B.part_id, CONVERT(char(6),B.date,112) AS code,
> B.total+ISNULL((
> SELECT SUM(C.amount)
> FROM C WHERE B.line_id=C.line_id
> ),0) as TotalPlusAmount
> FROM B
> ) X GROUP BY X.part_id, X.code
> ) Y INNER JOIN D ON Y.code = D.code
> GROUP BY Y.part_id
> Razvan
>|||Squirrel wrote:
> Frankly, your SQL statement is complicated to me. would you kindly explain
> it to me?
Read it from the inner-most query, like this:
First, we compute B.Total+SUM(C.Amount) for each row in B (using a
correlated subquery to get the sum of C.Amount, wrapped in an ISNULL,
just in case there are no rows in table C for a certain line_id).
Then we compute AnotherSum, as the sum of the TotalPlusAmount (the
value computed above), for each part_id and X.code; we defined earlier
that X.code is the month/year of B.date.
Then we join the above result to table D, on the column code, to get
the rate corresponding to each month/year. We compute TheSum as the sum
of AnotherSum (the value calculated above), multiplicated by the
corresponding rate, for each part_id.
Razvan|||Hi There,
You may like to try this one out exactly what razvan suggested. The
join of four tables seems reductant.
1) First taking B as base table find the sum(amount from C table ofr
lineids in B)
2) Join the derived table with D on code
3) Apply your formula (b.total+ sum(c.amt) )*rate
Select Der1.Part_ID , Sum(Tot) From (
Select Der.Part_id,Sum(Der.Total+Isnull(X,0))*D.Rate Tot From
(
Select B.part_id , b.Total ,
(
Select sum(C.amount) from C where C.line_id=B.line_id
) X ,
convert(char(6),date,112) Code from B
) Der
Inner Join D On D.Code=Der.Code
group by Der.Part_Id,D.rate
) Der1 Group by Part_id
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Squirrel wrote:
> Thanks, Razvan.
> Frankly, your SQL statement is complicated to me. would you kindly explain
> it to me?
> Thanks again.
> SC
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1151130774.295568.163480@.m73g2000cwd.googlegroups.com...

Agg op question

hi,

i have a table 'Details' with columns [id int; level varchar(20)]. i want to get two things from one single query:
1) the total number of items with Details.id=xxx
2) all the associated [level] text

i tried a few times doing things like:
SELECT [Level], COUNT(*) FROM Details WHERE id=xxx GROUP BY id

but it either gives me error "Column [Level] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

i am using MS SQL Server as the back engine and T-SQL in Access...

Help~

thanks in advancePerhaps you can do this:

select level, (select count(*) from details where id=xxx) as cnt
from details where id=xxx;

Works in Oracle, I don't know about your DBMS.|||Beautifully done~ thanks!