SQL GROUP BY ERROR MESSAGE

Hi Experts, I got the error message from the query below.

Column 'MakeMyDeal_com.dbo.Adobe_Periods.VDPImpressions' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't know how to fix the group by code. Can you help me on it. Thanks.

select 'MTD' as timeframe,
sum([mtd]) as dealstart,
(select CASE WHEN isnull(VDPIMPRESSIONS,0)=0 THEN 0 ELSE SUM(VDPIMPRESSIONS)END AS IMPRESSIONS from MakeMyDeal_com.[dbo].[Adobe_Periods] av inner join MakeMyDeal_com..dealer d on av.dealerid=d.DealerID where period ='MTD') as impressions,
(select CASE WHEN isnull(VDPPENCILS,0)=0 THEN 0 ELSE SUM(VDPPENCILS)END AS PENCILS from MakeMyDeal_com.[dbo].[Adobe_Periods] av inner join MakeMyDeal_com..dealer d on av.dealerid=d.DealerID where period ='MTD') as pencils,
(select Count(*) from makemydeal_com.."User"
WHERE  userid IN (SELECT userid
                  FROM   makemydeal_com..shopper)
       AND createdate >= Dateadd(MONTH, Datediff(MONTH, 0, CONVERT(DATE, Getdate())), 0)) as users
from [MMD_Feed].[dbo].[DART] d

Open in new window

tanj1035Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:

select 'MTD' as timeframe,
sum([mtd]) as dealstart,
(select SUM(isnull(VDPIMPRESSIONS,0)) AS IMPRESSIONS from MakeMyDeal_com.[dbo].[Adobe_Periods] av inner join MakeMyDeal_com..dealer d on av.dealerid=d.DealerID where period ='MTD') as impressions,
(select  SUM(isnull(VDPPENCILS,0)) AS PENCILS from MakeMyDeal_com.[dbo].[Adobe_Periods] av inner join MakeMyDeal_com..dealer d on av.dealerid=d.DealerID where period ='MTD') as pencils,
(select Count(*) from makemydeal_com.."User"
WHERE  userid IN (SELECT userid
                  FROM   makemydeal_com..shopper)
       AND createdate >= Dateadd(MONTH, Datediff(MONTH, 0, CONVERT(DATE, Getdate())), 0)) as users
from [MMD_Feed].[dbo].[DART] d
tanj1035Author Commented:
Thanks for your reply. I tried it before. The impressions and pencils do not have the value in the database, so the query returned null value.
The purpose of this question is trying to return 0 instead of null value.

Thanks.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can't understand the logic of your query.
How do you relate [MMD_Feed].[dbo].[DART] with the other tables?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

tanj1035Author Commented:
sum(mtd) refers to  [MMD_Feed].[dbo].[DART]
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's easy but how do you relate that table with the others (MakeMyDeal_com.[dbo].[Adobe_Periods], MakeMyDeal_com..dealer,  makemydeal_com.."User" and makemydeal_com..shopper)?
I.e. how you'll join it with one of those tables?
tanj1035Author Commented:
Other tables do not relate to [MMD_Feed].[dbo].[DART] . I was using subquery instead of temp table to create the query.
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's why there's no logic on your query.
What you want to achieve?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Will this work for you?
select sum(isnull(VDPIMPRESSIONS,0)) as impressions,
	sum(isnull(VDPPENCILS,0)) as pencils,
	(select Count(*) 
	from makemydeal_com.."User"
	WHERE userid IN (SELECT userid FROM makemydeal_com..shopper)
	AND createdate >= Dateadd(MONTH, Datediff(MONTH, 0, CONVERT(DATE, Getdate())), 0)) as users,
	(select sum([mtd]) from [MMD_Feed].[dbo].[DART]) as dealstart
from MakeMyDeal_com.[dbo].[Adobe_Periods] av 
	inner join MakeMyDeal_com..dealer d on av.dealerid=d.DealerID 
where period ='MTD'

Open in new window

tanj1035Author Commented:
Thanks for your reply.  The purpose of this question is trying to return 0 instead of null value for "impressions" and "pencils.
 I tried your query. Since the impressions and pencils do not have the value in the database, so the query returned null value.


 Thanks.
awking00Information Technology SpecialistCommented:
select sum(case when vdpimpressions is null then 0 else vdpimpressions end) as impressions
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
see if this works for you?

select timeframe, dealstart,
isnull(impressions, 0) impressions,
isnull(pencils, 0) pencils,
isnull(users, 0) users
from
(
 select 'MTD' as timeframe,
sum([mtd]) as dealstart,
(select SUM(isnull(VDPIMPRESSIONS,0)) AS IMPRESSIONS from MakeMyDeal_com.[dbo].[Adobe_Periods] av inner join MakeMyDeal_com..dealer d on av.dealerid=d.DealerID where period ='MTD') as impressions,
(select  SUM(isnull(VDPPENCILS,0)) AS PENCILS from MakeMyDeal_com.[dbo].[Adobe_Periods] av inner join MakeMyDeal_com..dealer d on av.dealerid=d.DealerID where period ='MTD') as pencils,
(select Count(*) from makemydeal_com.."User"
WHERE  userid IN (SELECT userid
                  FROM   makemydeal_com..shopper)
       AND createdate >= Dateadd(MONTH, Datediff(MONTH, 0, CONVERT(DATE, Getdate())), 0)) as users
from [MMD_Feed].[dbo].[DART] d
  ) a

Open in new window

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
In which table are stored the impressions and pencils columns?
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 SQL Server

From novice to tech pro — start learning today.