Possible error in a query syntax...

This query is from a book (and supposedly it is correct):
WITH cet1(SeminarMonth, Title, GrossSales)
As(
SELECT TOP 2 DATENAME(M, s.StartTime) As SeminarMonth s.Title,
s.Cost*Count(sa.Attendee) As GrossSales
FROM Seminar as JOIN SeminarAttendees sa 
ON s.SeminarID = sa.Seminar WHERE DATENAME(M s.StartTime) = 'November'
GROUP BY DATENAME(M, s.StartTime), s.Title, s.Cost 
ORDER BY GrossSales DESC
UNION

SELECT TOP 2 DATENAME(M, s.StartTime) As SeminarMonth s.Title,
s.Cost*Count(sa.Attendee) As GrossSales
FROM Seminar as JOIN SeminarAttendees sa 
ON s.SeminarID = sa.Seminar WHERE DATENAME(M s.StartTime) = 'December'
GROUP BY DATENAME(M, s.StartTime), s.Title, s.Cost 
ORDER BY GrossSales DESC)

SELECT * FROM cte1;

Open in new window

But I think ", s.Cost" in bold has to be removed from this code in two places:

GROUP BY DATENAME(M, s.StartTime), s.Title, s.Cost

Question: Do you also find , s.Cost will cause error if this query runs as it is?
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
I think it'll have a better chance if you'll apply the alias "s" to seminar.

WITH cet1(SeminarMonth, Title, GrossSales)
As(
SELECT TOP 2 DATENAME(M, s.StartTime) As SeminarMonth s.Title,
s.Cost*Count(sa.Attendee) As GrossSales
FROM Seminar s as JOIN SeminarAttendees sa 
ON s.SeminarID = sa.Seminar WHERE DATENAME(M s.StartTime) = 'November'
GROUP BY DATENAME(M, s.StartTime), s.Title, s.Cost 
ORDER BY GrossSales DESC
UNION

SELECT TOP 2 DATENAME(M, s.StartTime) As SeminarMonth s.Title,
s.Cost*Count(sa.Attendee) As GrossSales
FROM Seminar s as JOIN SeminarAttendees sa 
ON s.SeminarID = sa.Seminar WHERE DATENAME(M s.StartTime) = 'December'
GROUP BY DATENAME(M, s.StartTime), s.Title, s.Cost 
ORDER BY GrossSales DESC)

SELECT * FROM cte1;

Open in new window

0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
MY focus and question is on:  GROUP BY DATENAME(M, s.StartTime), s.Title, s.Cost
0
Kent OlsenData Warehouse Architect / DBACommented:
You probably want to replace s.Cost with s.Cost*Count(sa.Attendee) or GrossSales

It looks like the query has multiple issues.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
GrossSales as an alias cannot be used in Group By. It can be used only in Order By.

The logical order of execution is:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY    -- alias GrossSales cannot be used here
6. CUBE | ROLLUP
7. HAVING
8. SELECT        -- alias GrossSales  is define here
9. DISTINCT
10 ORDER BY  -- alias GrossSales can be used here
11. TOP
0
Kent OlsenData Warehouse Architect / DBACommented:
My apologies,

That's the column that's being aggregated.  The COUNT() function is either global (across all selected rows) or controlled by the GROUP BY.  Since the GROUP BY contains all of the other select columns, that should be correct.

Note though, that the name of the CTE is not the name used in the final select statement (cte1 and cet1).
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Typo, my apology, the book has it as

WITH cte1(SeminarMonth, Title, GrossSales)
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I tested another query

;with d
as
(
select productid, unitPrice*qty Purchase from Sales.OrderDetails
group by productid, unitPrice*qty
)
Select * from D

This works fine.
0
Kent OlsenData Warehouse Architect / DBACommented:
It should.  There's no aggregation in that query.

SELECT count(*), a, b, c, d*e FROM mytable GROUP BY a, b, c, d*e.

The GROUP BY clause needs to include all of the columns that control the grouping.  In SQL Server, DB2, and Oracle that is all of the scalar columns (MySQL allows you to use a subset of the scalar columns.)


Kent
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Correction on my last post. After I changed my second test to below code, it worked fine.

;with d
as
(
select productid, unitPrice*count(qty) Purchase from Sales.OrderDetails
group by productid, unitPrice
)
Select * from D

I know having count(qty) doesn't make much of sense. The idea here was mimic the original code and somehow use Count(someFieldName).


Works:             group by productid, unitPrice

Doesn't work: group by productid, unitPrice*count(qty)
0
Kent OlsenData Warehouse Architect / DBACommented:
This about what the query is asking

;with d
as
(
select productid, unitPrice*count(qty) Purchase from Sales.OrderDetails
group by productid, unitPrice
)
Select * from D

Is really just

select productid, unitPrice*count(qty) Purchase from Sales.OrderDetails
group by productid, unitPrice

If all of the line items in OrderDetails have the same price for the productid, that query will return the same results as:

select productid, unitPrice*count(qty) Purchase from Sales.OrderDetails
group by productid

Both queries will return the total revenue for each product.  The query with unitPrice in the GROUP BY clause will return total revenue by price by product.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.