为此
表#1:
create table Program_T
(AccountName varchar(150) not null unique,
ProgramID int not null,
Revenue int,
Advocates int,
Shares int,
Conversions int,
Impressions int,
LaunchDate date,
CSMID int not null,
constraint Program_PK primary key (AccountName, CSMID),
constraint Program_FK1 foreign key (AccountName) references Account_T(AccountName),
constraint Program_FK2 foreign key (CSMID) references CSM_T(CSMID));
表#2:
create table Account_T
(AccountName varchar(150) not null unique,
Health varchar(10) not null,
EcommercePlatform varchar(50),
CSMID int not null,
Industry varchar(50),
Amount int not null,
constraint Accounts_PK primary key (AccountName),
constraint Accounts_FK foreign key (CSMID) references CSM_T(CSMID));
我正在尝试编写一个查询,该查询将为我们提供每个电子商务平台订购的平均收入。
到目前为止,我有....
Select Revenue, EcommercePlatform
From Program_T, Account_T
Where Avg(Revenue)
Order by EcommercePlatform;
但是我遇到的错误说:
对组功能的使用无效",但我什至没有使用组 功能。
请建议
Select Avg(Revenue), EcommercePlatform
From Program_T inner join Account_T on Program_T.AccountName=Account_T.AccountName
group by EcommercePlatform;
这应该可以正常工作。
我想你想要:
select a.EcommercePlatform, Avg(p.Revenue)
grom Program_T p join
Account_T a
using (accountName)
group by a.EcommercePlatform;
注意:
- 学会使用正确的,显式,标准
JOIN
语法。 - 从不在
FROM
子句中使用逗号。 - 符合所有列参考的资格,尤其是在查询中引用多个表的情况下。
- 您想选择的列在
select
子句中,而不是where
子句。