Group by with criteria

I have a Problem with my Select statement!
I want to Select every Record, where ChNr fits the pattern the User
chooses. And sum up the different Source Columns.
So far this one works out fine, but I want to have the sum over ALL ChNr
that fit the pattern given, not a result for every ChNr.
If that is of any interest for you, ChNr always looks the same: three
numbers-three numbers e.g. 481-102, 581-235.
SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY ChNr;
With this Statement I get one Result for every single ChNr. What I want is
ONE result for all ChNr that look like 491-... .
I thought I would have to make a criteria in the Group by part, but I just
can't make it work! I already tried to put another LIKE thing into Group
by, but it just won't do.
Unfortunately the DataBase I am working with is Access.
I am working with Visual Studio and C#.
I hope I made myself clear. It is really not so easy to explain my problem.
Thanks Julia
Message posted via http://www.webservertalk.comIf I understand you correctly, this should do it:
SELECT LEFT(a.ChNr,3) as [NAME], Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL
25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY LEFT(ChNr);
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:2050d4f1fa8c4293b71ff00dc45057d1@.SQ
webservertalk.com...
>I have a Problem with my Select statement!
> I want to Select every Record, where ChNr fits the pattern the User
> chooses. And sum up the different Source Columns.
> So far this one works out fine, but I want to have the sum over ALL ChNr
> that fit the pattern given, not a result for every ChNr.
> If that is of any interest for you, ChNr always looks the same: three
> numbers-three numbers e.g. 481-102, 581-235.
> SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
> BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
> [BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
> (1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
> FROM tbAuswert a
> WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
> GROUP BY ChNr;
>
> With this Statement I get one Result for every single ChNr. What I want is
> ONE result for all ChNr that look like 491-... .
> I thought I would have to make a criteria in the Group by part, but I just
> can't make it work! I already tried to put another LIKE thing into Group
> by, but it just won't do.
> Unfortunately the DataBase I am working with is Access.
> I am working with Visual Studio and C#.
> I hope I made myself clear. It is really not so easy to explain my
> problem.
> Thanks Julia
> --
> Message posted via http://www.webservertalk.com|||The Idea is fine! This is exactly what I need!
I tried it and first I got the message of a missing parameter, so I looked
up left() and now my Statement looks like this:
SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY LEFT (a.Auswert, 4);
But now I get the following Message:
"You tried to execute a query that does not include the specified
expression 'ChNr' as part of an aggregate function"
So, what could might be the Problem now?
Message posted via http://www.webservertalk.com|||You cannot be grouping by left(col,4) and not including it as part of your
select.
So, change your SELECT DISTINCT a.ChNr to SELECT left(a.ChNr,4).
DISTINCT is redundant here when you do grouping.
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:b7da74bb92c645fb868096d31d45aa70@.SQ
webservertalk.com...
> The Idea is fine! This is exactly what I need!
> I tried it and first I got the message of a missing parameter, so I looked
> up left() and now my Statement looks like this:
> SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
> BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
> [BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
> (1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
> FROM tbAuswert a
> WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
> GROUP BY LEFT (a.Auswert, 4);
> But now I get the following Message:
> "You tried to execute a query that does not include the specified
> expression 'ChNr' as part of an aggregate function"
> So, what could might be the Problem now?
> --
> Message posted via http://www.webservertalk.com|||How blind can one woman be'
Thank you so much!!! It Works!
You are a genius! ;-)
Just another small question concerning the DISTINCT.
It might come up, that I have a record double except from the Key (which
are consecutive numbers ), doesen't DISTINCT filter the double out before
summing up?
Message posted via http://www.webservertalk.com|||;-) you're welcome.
See if this example helps:
create table #tmp(i sysname, j int)
insert #tmp select '123-456',1
insert #tmp select '123-456',1
insert #tmp select '123-456',2
insert #tmp select '123-456',2
insert #tmp select '123-456',2
insert #tmp select '456-456',2
insert #tmp select '456-456',3
insert #tmp select '789-456',5
insert #tmp select '789-456',5
insert #tmp select '789-456',1
go
--only distinct j
select left(i,4) i, sum(distinct j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
--regular
select left(i,4) i, sum(j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
--redundant distinct
select distinct left(i,4) i, sum(j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
go
drop table #tmp
go
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:e2ed74fec66e45c7b222a42b4f653e82@.SQ
webservertalk.com...
> How blind can one woman be'
> Thank you so much!!! It Works!
> You are a genius! ;-)
> Just another small question concerning the DISTINCT.
> It might come up, that I have a record double except from the Key (which
> are consecutive numbers ), doesen't DISTINCT filter the double out before
> summing up?
> --
> Message posted via http://www.webservertalk.com