HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.newusers » Help with SUM function
Topic: Re: Help with SUM function
To derive a sensible result, you need a useful dataset. i.e. a set of rows
that can be uniquely identified. With your dataset, how dow you distingush
same amounts with no key? If all you care is just the distinct set of
values, you can do:
SELECT SUM( val )
FROM ( SELECT DISTINCT val FROM tbl
WHERE id = 1 ) D ( val ) ;
--
Anith
Replies below ↓
Replies
Re: Help with SUM function
Sounds like your data is not normalized.
One way:
SELECT SUM(I$$$)
FROM (SELECT DISTINCT * FROM TheTable) as X
Or another:
SELECT SUM(I$$$)
FROM (SELECT ID, Job, MAX(I$$$) FROM TheTable GROUP BY ID, Job)
Of course there might be other issues with your actual data that are
not apparent in the sample, but either approach would work with the
data provided.
Roy Harvey
Beacon Falls, CT
On Tue, 29 Apr 2008 11:53:32 -0700 (PDT), rhaazy <rhaazy@gmail.com>
wrote:
>I have a table with something like this:
>
>ID.............Job..............$$$
>1...............A..................5
>1...............A..................5
>1...............B..................10
>1...............B..................10
>
>
>Right now I have this query:
>
>select SUM([$$$])
>from aTable
>where ID = '1'
>
>It obviously sums up all records and gives me value of 30.
>
>However I dont want it to work this way, I only want it to sum each
>Job group once, so for this example the sum would be 15.
Help with SUM function
I have a table with something like this:
ID.............Job..............$$$
1...............A..................5
1...............A..................5
1...............B..................10
1...............B..................10
Right now I have this query:
select SUM([$$$])
from aTable
where ID = '1'
It obviously sums up all records and gives me value of 30.
However I dont want it to work this way, I only want it to sum each
Job group once, so for this example the sum would be 15.
Re: Help with SUM function
No my data is not normalized...but not much about this project is
normal....
Thanks for your help, I found it very helpful in not only getting what
I needed immediately, but helped me better organize my data.
On Apr 29, 3:38=A0pm, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.net> wrote:
> Sounds like your data is not normalized.
>
> One way:
>
> SELECT SUM(I$$$)
> =A0 FROM (SELECT DISTINCT * FROM TheTable) as X
>
> Or another:
>
> SELECT SUM(I$$$)
> =A0 FROM (SELECT ID, Job, MAX(I$$$) FROM TheTable GROUP BY ID, Job)
>
> Of course there might be other issues with your actual data that are
> not apparent in the sample, but either approach would work with the
> data provided.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 29 Apr 2008 11:53:32 -0700 (PDT), rhaazy <rha...@gmail.com>
> wrote:
>
>
>
> >I have a table with something like this:
>
> >ID.............Job..............$$$
> >1...............A..................5
> >1...............A..................5
> >1...............B..................10
> >1...............B..................10
>
> >Right now I have this query:
>
> >select SUM([$$$])
> >from aTable
> >where ID =3D '1'
>
> >It obviously sums up all records and gives me value of 30.
>
> >However I dont want it to work this way, I only want it to sum each
> >Job group once, so for this example the sum would be 15.- Hide quoted tex=
t -
>
> - Show quoted text -