Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.
Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.
|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
RotheryTelephone call out26/09/05 13:00:00
GinnellyAuto Order 26/09/05 12:55:03
AldcroftCustomer Services26/09/05 12:55:00
AldcroftAuto Order 26/09/05 12:54:20
BroadbentAuto Reminder26/09/05 12:54:07
BroadbentAuto Reminder26/09/05 12:51:23
PicklesTelephone call out26/09/05 12:50:00
BroadbentAuto Reminder26/09/05 12:49:50
PicklesTelephone call in26/09/05 12:43:00
BroadbentAuto Reminder26/09/05 12:41:22
RaceAuto Appointment26/09/05 12:41:13
RaceAuto Appointment26/09/05 12:36:27
BroadbentAuto Reminder26/09/05 12:36:04
BroadbentAuto Reminder26/09/05 12:33:38
BroadbentAuto Reminder26/09/05 12:30:01
BoothTelephone call out26/09/05 12:25:32
BoothTelephone call out26/09/05 12:23:00
PriorAuto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.
|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>sql

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