# Thread: sum up the price and take the date where s=1 (was "Query-Problem")

1. Registered User
Join Date
Jan 2002
Location
Berlin
Posts
72

## Unanswered: sum up the price and take the date where s=1 (was "Query-Problem")

Hello,

I have the follwoing problem:

I`d like to generate a new table out of the follwing one:

ID1 PRICE DATE ID2 S
1C8GYN2M21U130908 10229.00 2005-11-07 00:00:00.000 135816 1
1FTDX07W0VKB74105 -1500.00 2005-04-15 00:00:00.000 126496 0
1FTDX07W0VKB74105 14950.00 2005-04-14 00:00:00.000 126496 1
1G6KY549X1U274486 21301.05 2006-01-17 00:00:00.000 138481 1

Result:
ID1 PRICE DATE ID2
1C8GYN2M21U130908 10229.00 2005-11-07 00:00:00.000 135816
1FTDX07W0VKB74105 13450.00 2005-04-14 00:00:00.000 126496
1G6KY549X1U274486 21301.05 2006-01-17 00:00:00.000 138481

If there is an ID with a S 1 and 0 then sum up the price and take the date where s=1.
Write the result into another table.

Can anybody help me ?
Do I have to implement this by using cursors ?

Thx.

Dajm

2. L O S T in Reality
Join Date
Nov 2005
Location
San Francisco, CA
Posts
506
Originally Posted by dajm
Hello,

I have the follwoing problem:

I`d like to generate a new table out of the follwing one:

ID1 PRICE DATE ID2 S
1C8GYN2M21U130908 10229.00 2005-11-07 00:00:00.000 135816 1
1FTDX07W0VKB74105 -1500.00 2005-04-15 00:00:00.000 126496 0
1FTDX07W0VKB74105 14950.00 2005-04-14 00:00:00.000 126496 1
1G6KY549X1U274486 21301.05 2006-01-17 00:00:00.000 138481 1

Result:
ID1 PRICE DATE ID2
1C8GYN2M21U130908 10229.00 2005-11-07 00:00:00.000 135816
1FTDX07W0VKB74105 13450.00 2005-04-14 00:00:00.000 126496
1G6KY549X1U274486 21301.05 2006-01-17 00:00:00.000 138481

Dajm

Try this...

USE pubs
GO
CREATE TABLE tt(id1 VARCHAR(10),
price INT,
dat DATETIME,
id2 VARCHAR(10),
s1 BIT)

INSERT tt VALUES('e1',1289.43,getdate(),'ee1',1)

INSERT tt VALUES('e2',333.43,getdate(),'ee2',1)

INSERT tt VALUES('e1',1200.43,getdate(),'ee1',1)

INSERT tt VALUES('e1',-444.43,'2006-01-03 14:53:46.817','ee1',0)
INSERT tt VALUES('e4',44.43,getdate(),'ee4',1)

SELECT h.id1,sum(h.price)as price,max(h.dat) as date,h.id2 FROM tt h
GROUP BY h.id1,h.id2

DROP TABLE tt
Last edited by rudra; 04-03-06 at 05:39.

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
rudra, your query does not take the date where s=1 as requested

4. Registered User
Join Date
Jan 2002
Location
Berlin
Posts
72

## sum up

So, R937,

Do you have another suggestion ?

Dajm

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
what happens when there is only s=0?

what happens when there is more than one s=1?

6. Registered User
Join Date
Jan 2002
Location
Berlin
Posts
72

## sum up the price and take the date where s=1

Hi r937,

this is a valid question:

s=1 will always be there
s=1 more often than one time does not happen
s=0 more often than one time can happen and need to be summed up like written before

Dajm

7. L O S T in Reality
Join Date
Nov 2005
Location
San Francisco, CA
Posts
506
Originally Posted by dajm
Hi r937,

this is a valid question:

s=1 will always be there
s=1 more often than one time does not happen
s=0 more often than one time can happen and need to be summed up like written before

Dajm
so, s1 will be the last date or the first date?? If last then max will do if first then min will do.I would like to hear Rudy's view on it?

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
max and min will be the same
Code:
```select ID1
, sum(PRICE) as PRICE
, max(case when S=1 then [DATE] end) as [DATE]
, ID2
from daTable
group
by ID1
, ID2```

9. L O S T in Reality
Join Date
Nov 2005
Location
San Francisco, CA
Posts
506
Originally Posted by r937
max(case when S=1 then [DATE] end) as [DATE]
hmmm....

10. Registered User
Join Date
Jan 2002
Location
Berlin
Posts
72
Sry to say,

but always take the date where s=1, could be the first, could be the last...

Thx for ur suggestions up to now, but it begins to open my eyes...

Dajm

11. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by dajm
Sry to say,
why are you sry?

did you try my query?

by the way, there is no "first" or "last" in a database table

12. L O S T in Reality
Join Date
Nov 2005
Location
San Francisco, CA
Posts
506
Originally Posted by dajm
Sry to say,

but always take the date where s=1, could be the first, could be the last...

Thx for ur suggestions up to now, but it begins to open my eyes...

Dajm
what sorry? I think Rudy has already given you the solve...
Last edited by rudra; 04-03-06 at 08:02.

13. Registered User
Join Date
Jan 2002
Location
Berlin
Posts
72
Aeh, yes, was too late...

Thx a lot Rudy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•