Group By Clause Help

Hello the code below shows multiple instances of targets.name "donor type" I could not correclty run the code without including contributions.program. I would like the output to only have 1 value for each donor type. How would I do this or workaround to get it done?

- thanks for your time.

SQL> SELECT targets.name "DONOR TYPE", contribution.program,
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name, contributions.program;

DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
Foundations Applied Research 175
Individuals Basic Research 50
Corporate Donors International Programs 100
Corporate Donors Teaching Programs 50
Foundations Teaching Programs 50What prevents you from doing this?:

SQL> SELECT targets.name "DONOR TYPE",
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name;

DONOR TYPE CONTRIBUTION QTR2
------- ------
Corporate Donors 250
Foundations 225
Individuals 50|||How can I get tthe output to look like this?

DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
International Programs 100
Teaching Programs 50

Foundations Applied Research 175
Teaching Programs 50
Individuals Basic Research 50

- thanks for your help|||Oh I see, you mean suppress the output of the repeated value?

In SQL Plus, use:

SQL> BREAK ON "DONOR TYPE"

Also, add "ORDER BY targets.name, contributions.program" after the GROUP BY clause to be sure the ordering is correct (GROUP BY doesn't guarantee the order).|||Great this worked for that table--thanks a bunch. How can I group by contrubuion by member only so there is only one isntance per name and the sum of all rows info and still maintain each of the columns. this can be easily done by removing the target column but I need to display it along with the others belwo. Once put the target column in I must also gruoup by member.qtr1 which produces the multiple row output. How can I work around this to only group by member?

SQL> SELECT contribution.member, member.qtr1 "TARGET",
2 SUM(contribution.amount) "CONT. QTR1",
3 ROUND(SUM(contribution.amount)/member.qtr1,3)*10 "% OF PROJECTION"
4 FROM contribution, member
5 WHERE contribution.cdate >= TO_DATE('01/01/03', 'MM/DD/YY')
6 AND contribution.cdate <= TO_DATE('03/31/03', 'MM/DD/YY')
7 GROUP BY contribution.member, member.qtr1;

MEMBER TARGET CONT. QTR1 % OF PROJECTION
----- ---- ---- -----
Adams 50 175 35
Adams 75 175 23.33
Adams 100 175 17.5
Adams 150 175 11.67
Adams 200 175 8.75
Adams 250 175 7
Baker 50 100 20
Baker 75 100 13.33
Baker 100 100 10
Baker 150 100 6.67
Baker 200 100 5

Grid Computing for Sql Server 2005?

Probably a bad way to start but wondering if Sql Server 2005 scales the way Grid computing does (ie several instances attached to same DB) by adding cheap hardware which is load balancing requests. Or is there only the option of adding more CPU's to the existing frame to scale the Sql Server 2005 DB?

We are migrtating to 2005 from 2000 and have several questions about maintaining a sql server 2000 failback DB that is kept in sync with the new 2005 DB after it goes production.

I was hoping to be able to detach a 2000 database and re-attach it to 2000 but seems not to work or at least backup and restore the 2005 databases and recover into 2000 but don't see that option. At last resort using something like DataMirror to maintain a synced version of DB in sql server 2000. Any suggestions or help here would be very appreciated.

We are planning side-by-side upgrade path currently running IA64 on unisys. This is a large 450gig OLTP Database.

Also we do replication and seems that Log reader really pounds the disk where logs are located. Suggestions on how to tune/reduce I/O on that disk? Have read plenty of papers and we are taking the 15 steps but wonder if 2005 does a better job of replication using the log files.

Thank You Very Much

sqlserver is builtin on "shared-nothing" technology. There is no way to get multiple instances of sqlserver to mount a same database for read_write. However, with sql2k5, you now have a way to allow multiple instances of sqlserver sharing a same database for readonly (i.e. scalable shared database).

http://support.microsoft.com/?kbid=910378|||

Unfortunately, you cannot use DB mirroring between SQL Server 2005 and SQL Server 2000. You cannot detach from SQL 2005 and reattach to SQL 2000. You also cannot backup from SQL 2005 and restore to SQL 2000. Basically, going from SQL 2000 to SQL 2005 is a one way trip.

Why do you want to keep a fallback SQL Server 2000 database? SQL Server 2005 has been out for nearly a year, and SP1 has been out for several months now. SQL Server 2005 is very solid now. You can run the Mcrosoft SQL Server Best Practices Analyzer on your existing SQL Server 2000 database to identify possible issues in upgrading to SQL Server 2005, and you can also run the Upgrade Advisor. You can and should do as much testing as possible to make sure that your application runs without issue on SQL Server 2005 before you upgrade in production. One common issue is inline SQL that uses non-ansi outer joins, which will fail on SQL Server 2005 in 90 compatibility mode.

I think a side by side upgrade (by running SQL 2005 as a named instance) on the same server is not the best idea. If you have the budget, you would be much better off to have a new server with a fresh copy of the OS to install SQL 2005 as the default instance. Then, if you can afford an outage, you can detach your SQL Server 2000 database, copy the files to another location on the SAN, and then attach in SQL 2005 on the new server.

If you cannot afford a long enough outage to copy the data files, you can simply detach from SQL Server 2000, then attach in SQL Server 2005. This is more risky, because you cannot go back if there is a problem. You would have to restore to SQL Server 2000 from a backup.

Basically, you either have to take a fairly long outage or take some risk when you upgrade. Spending money on a second new server can reduce the risk or reduce the length of the outage.