Error in subquery

See query below.  Gives syntax error in subquery.

See if anyone catches the issue before I do.

SELECT A.AVAYA_NAME, A.[AVPROD_SCHEDULER PERSON NAME], A.AVPROD_SCHEDULER_ID, A.Posting_Date,
(select count(*) from qryScheduler B where A.AVPROD_SCHEDULER_ID= B.AVPROD_SCHEDULER_ID and A.Posting_Date = B.Posting_Date and B.[CURRENT STATUS]='Scheduled')
FROM qryScheduler A
group by A.AVAYA_NAME, A.[AVPROD_SCHEDULER PERSON NAME], A.AVPROD_SCHEDULER_ID, A.Posting_Date,
(select count(*) from qryScheduler B where A.AVPROD_SCHEDULER_ID= B.AVPROD_SCHEDULER_ID and A.Posting_Date = B.Posting_Date and B.[CURRENT STATUS]='Scheduled')
patriotpacerAsked:
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.

PadawanDBAOperational DBACommented:
I'd give this a try (I'm assuming the SQL syntax is compatible with this, but it will run more efficiently than two subqueries):
SELECT 
	A.AVAYA_NAME, 
	A.[AVPROD_SCHEDULER PERSON NAME], 
	A.AVPROD_SCHEDULER_ID, 
	A.Posting_Date,
	C.record_count
FROM 
	qryScheduler as A
	cross apply( select count( * ) as record_count from qryScheduler as B where A.AVPROD_SCHEDULER_ID = B.AVPROD_SCHEDULER_ID and A.Posting_Date = B.Posting_Date and B.[CURRENT STATUS]='Scheduled' ) as C
group by 
	A.AVAYA_NAME, 
	A.[AVPROD_SCHEDULER PERSON NAME], 
	A.AVPROD_SCHEDULER_ID, 
	A.Posting_Date,
	C.record_count;

Open in new window

0
patriotpacerAuthor Commented:
I get this...

"Syntax error in FROM clause"
0
PadawanDBAOperational DBACommented:
Is that before or after I changed the aliases on the cross apply?  I meant to say that in an edit to the post.  Also, I neglected to answer your original question.  You can't use a subquery in the group by clause - it returns error 144 (which I am betting is what you're getting).  If you have the updated aliases in your query, let me know, you may have to use joins to accomplish the same result.  Also, if you need to maintain the null values out of qryScheduler (aliased as A), you'll want to use an outer apply instead of cross.
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

patriotpacerAuthor Commented:
I tried without the group by and it prompted saying I needed a group by .  I need the extra column of "Scheduled" to be either the total count or zero.
0
PadawanDBAOperational DBACommented:
Did it work with the apply?  To account for the total count or zero, you would want:
SELECT 
	A.AVAYA_NAME, 
	A.[AVPROD_SCHEDULER PERSON NAME], 
	A.AVPROD_SCHEDULER_ID, 
	A.Posting_Date,
	case
           when C.record_count is null then 0
           else C.record_count
        end as Scheduled
FROM 
	qryScheduler as A
	outer apply( select count( * ) as record_count from qryScheduler as B where A.AVPROD_SCHEDULER_ID = B.AVPROD_SCHEDULER_ID and A.Posting_Date = B.Posting_Date and B.[CURRENT STATUS]='Scheduled' ) as C
group by 
	A.AVAYA_NAME, 
	A.[AVPROD_SCHEDULER PERSON NAME], 
	A.AVPROD_SCHEDULER_ID, 
	A.Posting_Date,
	C.record_count;

Open in new window

0
patriotpacerAuthor Commented:
error
0
PadawanDBAOperational DBACommented:
instead of the case statement (not sure if there's a syntax difference or something), can you try:

SELECT 
	A.AVAYA_NAME, 
	A.[AVPROD_SCHEDULER PERSON NAME], 
	A.AVPROD_SCHEDULER_ID, 
	A.Posting_Date,
	coalesce( C.record_count, 0 ) as scheduled
FROM 
	qryScheduler as A
	outer apply( select count( * ) as record_count from qryScheduler as B where A.AVPROD_SCHEDULER_ID = B.AVPROD_SCHEDULER_ID and A.Posting_Date = B.Posting_Date and B.[CURRENT STATUS]='Scheduled' ) as C
group by 
	A.AVAYA_NAME, 
	A.[AVPROD_SCHEDULER PERSON NAME], 
	A.AVPROD_SCHEDULER_ID, 
	A.Posting_Date,
	C.record_count;

Open in new window

0
mbizupCommented:
Try this:

SELECT A.AVAYA_NAME, A.[AVPROD_SCHEDULER PERSON NAME], A.AVPROD_SCHEDULER_ID, A.Posting_Date, count(*)
from qryScheduler B INNER JOIN qryScheduler A 
ON A.AVPROD_SCHEDULER_ID= B.AVPROD_SCHEDULER_ID 
WHERE A.Posting_Date = B.Posting_Date and B.[CURRENT STATUS]='Scheduled'
group by A.AVAYA_NAME, A.[AVPROD_SCHEDULER PERSON NAME], A.AVPROD_SCHEDULER_ID, A.Posting_Date

Open in new window

0
patriotpacerAuthor Commented:
>> you may have to use joins to accomplish the same result.

That's what I ended up doing.


>>mbizup

thank you for the tip.  You basically confirmed things.
0
mbizupCommented:
The suggestions posted by PadawanDBA  (Apply, Coalesce, Case, etc)  are not valid syntax for Microsoft Access databases.... which is why you were having so much trouble with this question.
0
patriotpacerAuthor Commented:
I figured there was an assumption of SQL server.

But he was nonetheless right in stating what I was trying would need multiple queries.
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
Microsoft Access

From novice to tech pro — start learning today.