Summing the Sum of variants in Select Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Summing the Sum of variants in Select Statement

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-07-30 : 08:28:47

Here's my current sql statement:
SELECT ID, desc, SUM(QUANT) as xQuant FROM TEST GROUP BY ID

The ID has variants, AB001HB, AB001SB, etc. Right now the statement shows the sum of the quant for each variant. I want to show the sum of the quantity (quant) of the substring(ID,1,5), thus combining the sums for the variants. What changes should I make to the sql statement to make this work?

Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-30 : 08:30:18

[code]SELECT substring(ID,1,5), SUM(QUANT) as xQuant
FROM TEST
GROUP BY substring(ID,1,5)[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-30 : 08:31:11

SELECT substring(ID,1,5), desc, SUM(QUANT) as xQuant FROM TEST GROUP BY substring(ID,1,5)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-30 : 08:31:24

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-30 : 08:32:05

SELECT substring(ID,1,5), desc,SUM(QUANT) FROM TEST
GROUP BY substring(ID,1,5),desc

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-30 : 08:32:56

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-30 : 08:33:44

Oh dear ! What have I done ?

KH
[spoiler]Time is always against us[/spoiler]

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-07-30 : 08:38:57

That's what I thought too. However I'm getting an error message that says it's an illegal Group by clause. Unfortunately this is not SQL Server SQL statement, it's a Visual Foxpro 9 SQL statement. Is there a work around?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-30 : 08:40:13

quote:
Originally posted by RichardSteele

That's what I thought too. However I'm getting an error message that says it's an illegal Group by clause. Unfortunately this is not SQL Server SQL statement, it's a Visual Foxpro 9 SQL statement. Is there a work around?

Post your question at Foxpro Forums

Madhivanan

Failing to plan is Planning to fail

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-07-30 : 08:45:42

Have done this. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources