Regarding Aggregate conditions ..

Hai frns small help needed.

I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.

One special condition is as follows:

For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored.
If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used.
Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.

Here is the query is used

select * from sample

id ssn credit flag sem
1 101 0 C9 0
2 101 4 C9 3
3 101 4.5 C9 2
4 101 3.5 C1 1
5 102 4.2 C3 3
6 103 0 C1 2

select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2
group by ssn,flag

ssn flag sum_val
101 C1 3.5
103 C1 2.0
102 C3 4.2
101 C9 8.5

The above output is wrong one.

Expected output

101 4.5+3.5=8.0
102 4.2
103 2.0

Any help would be appreciated

Regards,SELECT ssn
, SUM(max_credit)
FROM --MAX credit\ sem per ssn & flag
(SELECT dbo.my_table.ssn
, max_credit = MAX(CASE WHEN credit = 0 THEN sem ELSE credit END)
FROM dbo.my_table
GROUP BY dbo.my_table.ssn
, dbo.my_table.flag) AS mc
GROUP BY ssn|||Actually I think that is flawed. I don't think your sample data is comprehensive enough.|||Thanks, Here is more sample data

101 0 C9 0
101 4 C9 3
101 4.5 C9 2
101 0 C9 2
101 3.5 C1 1
101 3.5 C2 2
104 3.5 C1 3
105 3.5 C2 0
106 3.5 C3 1
107 3.5 C4 1
109 3.5 C6 4
110 3.5 C7 1

Regards,|||SELECT ssn
, SUM(case when max_credit = 0
then sem_for_max_credit
else max_credit end) as daSum
FROM (
SELECT ssn
, flag
, sem as sem_for_max_credit
, credit as max_credit
FROM daTable as T
WHERE credit =
( SELECT MAX(credit)
FROM daTable
WHERE ssn = T.ssn
AND flag = T.flag )
) AS maxes
GROUP
BY ssn|||Thanks for the help.sql

Referencing a Cell

In reporting services, is there a way to reference a cell?
I need to get an aggregate of the first column of a report. In order to do
it, I was thinking of hiding the value in the first row and then at the end i
will sum up the total of that value in the first row....
I'm wondering if I can resolve the problem by referencing the value of
another cell rather than doing the sum in this cell (like you can in Excel)
as reporting service wont let me....
Thanks for your response...!!Can you give us a laid out example of what you want to do and what you want
to sum?
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"SQLDBA" <SQLDBA@.discussions.microsoft.com> wrote in message
news:63FB27B1-9E10-4C4A-991B-AF54314A3AF2@.microsoft.com...
> In reporting services, is there a way to reference a cell?
> I need to get an aggregate of the first column of a report. In order to do
> it, I was thinking of hiding the value in the first row and then at the
> end i
> will sum up the total of that value in the first row....
> I'm wondering if I can resolve the problem by referencing the value of
> another cell rather than doing the sum in this cell (like you can in
> Excel)
> as reporting service wont let me....
> Thanks for your response...!!
>|||Hi,
You can use the below syntax to sum the values of two or more cells in the
same manner.
= ReportItems!textbox50.Value + ReportItems!textbox51.Value
But you may get some errors notifying that the cells are not in the same
aggregate group.
But from your explanation I guess that you can use Grouping and then use the
below syntax
=Sum(Fields!FieldName1.Value)
Eralper
http://www.kodyaz.com
"Mary Bray [MVP]" wrote:
> Can you give us a laid out example of what you want to do and what you want
> to sum?
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "SQLDBA" <SQLDBA@.discussions.microsoft.com> wrote in message
> news:63FB27B1-9E10-4C4A-991B-AF54314A3AF2@.microsoft.com...
> > In reporting services, is there a way to reference a cell?
> >
> > I need to get an aggregate of the first column of a report. In order to do
> > it, I was thinking of hiding the value in the first row and then at the
> > end i
> > will sum up the total of that value in the first row....
> >
> > I'm wondering if I can resolve the problem by referencing the value of
> > another cell rather than doing the sum in this cell (like you can in
> > Excel)
> > as reporting service wont let me....
> >
> > Thanks for your response...!!
> >
>
>

Reference an aggregate in where clause

I have two tables that are joined on 1 column.

I want to update one table using an aggregate from the other table.

I could just update all rows even if the aggregate value has not changed, but I wondered if there was a way to only update the records where the aggregate has changed.

I tried this code but assigning the name m to aggreate gives an error

UPDATE t2 SET t2.ColumnB = SELECT MAX(ColumnB) AS m From Table t1

JOIN t2 ON t1.ColumnA = t2.ColumnA

WHERE t2.ColumnB <> m

GROUP BY t1.ColumnA

Niall:

Does this meet your needs?

set nocount on

declare @.t1 table (ColumnA char(1), ColumnB int)
declare @.t2 table (ColumnA char(1), ColumnB int)

insert into @.t1 values ('A', 5)
insert into @.t1 values ('B', null)
insert into @.t1 values ('C', null)
--select * from @.t1

insert into @.t2 values ('A', 3)
insert into @.t2 values ('A', 5)
insert into @.t2 values ('A', 7)
insert into @.t2 values ('B', 2)
--select * from @.t2

set nocount off

update @.t1
set ColumnB = xt.columnb
from @.t1 as yt
inner join
( select t1.ColumnA,
max (t2.columnB) columnB
from @.t1 as t1
inner join @.t2 as t2
on t1.columnA = t2.columnA
group by t1.columnA
) xt
on xt.columnA = yt.columnA

set nocount on

select * from @.t1

-- --
-- Output
-- --

-- (2 row(s) affected)

-- ColumnA ColumnB
-- - --
-- A 7
-- B 2
-- C NULL

|||

Use a subquery - replace the 0 in the isnull calls with blank strings if your ColumnB is character data.

UPDATE Table2 SET ColumnB = t1.MaxB
FROM Table2 t2
INNER JOIN
(SELECT ColumnA, MAX(ColumnB) AS MaxB
FROM Table1
GROUP BY ColumnA) t1 ON t2.ColumnA = t1.ColumnA
WHERE isnull(t2.ColumnB, 0) <> isnull(t1.MaxB, 0)