Emergency help 2. - SQL Server Forums

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

Emergency help 2.

Author

Topic

tbc
Starting Member

3 Posts

Posted - 2006-12-04 : 22:50:23

I don't know why the last topic is locked, but here is the situation more detailed.

I know that the group by is the answer for my last question.

But the situation is sadly not this easy:

We have a Donortype table and it looks like this:

D DONORTARGETQ1 DONORTARGETQ2 DONORTARGETQ3 DONORTARGETQ4
- ------------- ------------- ------------- -------------
C 400 400 500 500
F 300 350 400 500
I 125 150 200 275

3 rows selected.

The first column is called donortype. That's what we have to use.

The contribution table looks like this:

CONTRIBUT DONORID CONTRIBUTIONAMOUNT PROGRAMNAME MEMBERNUMB
--------- ---------- ------------------ ------------------------------ ----------
03-JAN-06 2 200 Basic Research 5
07-JAN-06 3 150 Applied Research 3
27-JAN-06 7 50 Teaching Programs 1
05-FEB-06 6 100 International Programs 2
23-FEB-06 2 50 International Programs 1
03-MAR-06 1 100 Basic Research 6
30-MAR-06 5 75 Basic Research 1
15-APR-06 6 50 Teaching Programs 4
09-MAY-06 4 100 Applied Research 2
14-MAY-06 7 100 International Programs 5
29-MAY-06 7 100 Applied Research 5
11-JUN-06 5 50 Teaching Programs 5
18-JUN-06 6 75 Applied Research 2
23-JUN-06 3 50 Basic Research 3
10-JUL-06 1 100 Teaching Programs 3
16-JUL-06 4 100 International Programs 2
22-JUL-06 6 75 Applied Research 4
15-AUG-06 5 60 Applied Research 1
23-AUG-06 2 150 International Programs 1
09-SEP-06 3 175 Applied Research 6
18-SEP-06 6 150 Teaching Programs 2
01-OCT-06 2 80 International Programs 5
13-OCT-06 1 50 Basic Research 6
30-OCT-06 5 25 Applied Research 1
11-NOV-06 4 100 Teaching Programs 4
24-NOV-06 2 100 Applied Research 5
10-DEC-06 7 200 Basic Research 1
20-DEC-06 3 85 International Programs 6

28 rows selected.

We have the DonorID and the ContributionAmount here.

We also have a Donor table:

DONORID DONORNAME D DONORPROJECTION
---------- ------------------------------ - ---------------
1 Crabtree I 50
2 Genetix C 100
3 Marlo I 55
4 Mervis F 60
5 Orange Computers C 50
6 Roper Foundation F 70
7 Keltron, Inc. C 85

7 rows selected.

The third column is DonorType as well.

What I have to create is something like this

DonorType ContributionAmount
I 100
F 300
C 250

Where the 100 is the sum of ContributionAmounts performed by DonorID-s which have a DonorType I

and 300 is the sum of ContributionAmounts performed by DonorID-s which have a DonorType F

and so on...

(the numbers are made up, they are probably not correct...)

My idea was that let's create a view with DonorID and sum(ContributionAmount) grouped by DonorID.

Than join this view with the DonorType table, and create a view which contains DonorId, DonorType and sum(ContributionAmount) from the first view.

And what now?! HOw do you group this by DonorType?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-12-04 : 23:03:14

Your last topic was locked because we don't do peoples homework for them.
If you are studying a degree that puts you into this industry, and you don't have the understanding or the ability to learn this so that you end up in a situation that it's the night before your assignment is due and you don't have the faintest clue how to do the work, then us giving you the answer isn't going to help you, your class, or our profession.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources