SQL Group by的问题

[ak47zy]

1.有3个表：
GOOGS G#,GNAME,PRICE 商品编号 名字 价格
SHOP S#,SNAME,AREA,MGR_NAME 商店编号 商店名 区域 经理名
SALE S#,G#,QUANTITY 商店编号 商品编号 销售数量

2.查询结果：
查询区域名为“济南”的所有的店的商店编号 商店名，总销售数量、总的销售金额

CREATE TABLE [dbo].[GOODS](
	[G#] [varchar](50) NULL,
	[GNAME] [varchar](50) NULL,
	[PRICE] [money] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[SALE](
	[S#] [varchar](50) NULL,
	[G#] [varchar](50) NULL,
	[QUANTITY] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[SHOP](
	[S#] [varchar](50) NULL,
	[SNAME] [varchar](50) NULL,
	[AREA] [varchar](50) NULL,
	[MGR_NAME] [varchar](50) NULL
) ON [PRIMARY]

--我的查询语句是
select a.s#,sname,sum(QUANTITY) as SUM_QUANTITY ,sum(QUANTITY)*price as SUM_MONEY from shop a,goods b,sale c
 where a.s#=c.s# and c.g#=b.g# and area='济南'
 group by b.g#,gname

--消息 8120，级别 16，状态 1，第 1 行
列 'a.S#' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
消息 8120，级别 16，状态 1，第 1 行
列 'a.SNAME' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
消息 8120，级别 16，状态 1，第 1 行
列 'b.PRICE' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

查询语句不对，请指正！

[东那个升]

select a.s#,sname,sum(QUANTITY) as SUM_QUANTITY ,sum(QUANTITY*price) as SUM_MONEY from shop a,goods b,sale c
 where a.s#=c.s# and c.g#=b.g# and area='济南'
 group by a.s#,sname

[ws_hgo]

select a.s#,sname,sum(QUANTITY) as SUM_QUANTITY ,sum(QUANTITY)*price as SUM_MONEY from shop a,goods b,sale c
 where a.s#=c.s# and c.g#=b.g# and area='济南'
 group by a.s#,sname

最好是给点测试数据

[黄_瓜]

select a.s#,sname,sum(QUANTITY) as SUM_QUANTITY ,sum(QUANTITY)*price as SUM_MONEY from shop a,goods b,sale c
where a.s#=c.s# and c.g#=b.g# and area='济南'
group by b.g#,gname

[liyaohui13098452233]

呵呵……

[feixianxxx]

select s.S#,s.SNAME,SUM(QUANTITY) as 销售数量,SUM(QUANTITY*PRICE) as 销售总额
from SHOP s join SALE sa on s.S#=sa.S#
            join GOODS g on sa.G#=g.G#
where AREA='济南'
group by s.S#,s.SNAME

[nianran520]

select a.s#,a.sname,sum(QUANTITY) as SUM_QUANTITY ,sum(QUANTITY)*b.price as SUM_MONEY from shop a,goods b,sale c
 where a.s#=c.s# and c.g#=b.g# and area='济南'
 group by a.s#,a.sname,b.g#,gname,b.price

[ak47zy]

挺好的！