asked on

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

Ryan Chong

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

tanj1035

ASKER

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ão

I can't understand the logic of your query.
How do you relate [MMD_Feed].[dbo].[DART] with the other tables?

tanj1035

ASKER

sum(mtd) refers to [MMD_Feed].[dbo].[DART]

Vitor Montalvão

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?

tanj1035

ASKER

Other tables do not relate to [MMD_Feed].[dbo].[DART] . I was using subquery instead of temp table to create the query.

Vitor Montalvão

That's why there's no logic on your query.
What you want to achieve?

Vitor Montalvão

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

tanj1035

ASKER

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.

awking00

select sum(case when vdpimpressions is null then 0 else vdpimpressions end) as impressions

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

In which table are stored the impressions and pencils columns?