Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group By - question


Group By - question

Author
Message
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 774
I have a question which is probably really basic but can't work it out for the life of me so i thought i'll ask for some help. Below is a rough sketch of how my database is there are more details but principle is the same.

In my database i have a table called "Meetings" & in my query i want to group all my meetings that are possible such as below.

DATE | TypeOfMeeting | NameOfMeeting

20120801 private Review
20120801 private Review
20120801 group Review
20120801 group Review
20120801 group Review
20120801 Conference Call Review
20120801 Conference Call Review


SELECT
[DATE],
[TypeofMeeting],
[NameofMeeting]
FROM
dbo.meetings
GROUP BY
[DATE],
[TypeofMeeting],
[NameofMeeting]


Only thing is in "TypeOfMeeting" i have the options of

- Group
- Conference Call
- Private


I want to group all the meetings except "Private" so if i have the same Date,TypeOfMeeting,NameofMeeting for Group or Conference Call they should all be groups for people involved. However if its Private then it should not be grouped and all the Private meetings should be seperated.

So the expected result should be:


DATE | TypeOfMeeting | NameOfMeeting

20120801 private Review
20120801 private Review
20120801 group Review
20120801 Conference Call Review



How do i ignore private from the group by results?
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 774
Update:

I got around the issue by just doing a quick fix, just made 2 seperate scripts one to only be "Private" and one to exclude only "Private" in my where condition.

I'm Not to worried about the permanent fix to my problem as it was a once off so thats fine, but would like to know for future reference if that is possible to achieve what has been requested or seperate scripts is the only way to go.
Steve Thompson-454462
Steve Thompson-454462
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 2057
Not exactly sure that I follow your requirements, but it sounds like you could use UNION to combine two data sets, one with a GROUP BY and one without. You're not including any aggregate logic in your select, so I assume that in this instance you're using GROUP BY to achieve the same effect as DISTINCT and avoid repeated rows in the non-private list of meetings (??).


SELECT
[DATE],
[TypeofMeeting],
[NameofMeeting]
FROM
dbo.meetings
WHERE
[TypeofMeeting] = 'private'

UNION ALL

SELECT
[DATE],
[TypeofMeeting],
[NameofMeeting]
FROM
dbo.meetings
WHERE
[TypeofMeeting] <> 'private'
GROUP BY
[DATE],
[TypeofMeeting],
[NameofMeeting]


laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
This should do what you want:



--I have a question which is probably really basic but can't work it out for the life of me so i thought i'll ask for some help. Below is a rough sketch of how my database is there are more details but principle is the same.

--In my database i have a table called "Meetings" & in my query i want to group all my meetings that are possible such as below.

--========= TEST DATA =============
declare @Meetings table ([DATE] Char(8), TypeOfMeeting Varchar(20), NameOfMeeting Varchar(10));

insert @Meetings values ( '20120801', 'private', 'Review' );
insert @Meetings values ( '20120801', 'private', 'Review' );
insert @Meetings values ( '20120801', 'group', 'Review' );
insert @Meetings values ( '20120801', 'group', 'Review' );
insert @Meetings values ( '20120801', 'group', 'Review' );
insert @Meetings values ( '20120801', 'Conference Call', 'Review' );
insert @Meetings values ( '20120801', 'Conference Call', 'Review' );

select * from @Meetings;

/*
DATE | TypeOfMeeting | NameOfMeeting

20120801 private Review
20120801 private Review
20120801 group Review
20120801 group Review
20120801 group Review
20120801 Conference Call Review
20120801 Conference Call Review
*/

--========= SOLUTION =============
-- Use 2 selects, 1 grouped for [TypeofMeeting] <> 'private',
-- & 1 not grouped for [TypeofMeeting] = 'private', & UNION them together.
-- NOTE: You MUST use UNION ALL, or duplicate rows are removed from the result set -
-- so only 1 'private' would appear.
SELECT
[DATE],
[TypeofMeeting],
[NameofMeeting]
FROM
@meetings
WHERE [TypeofMeeting] <> 'private'
GROUP BY
[DATE],
[TypeofMeeting],
[NameofMeeting]
UNION ALL
SELECT
[DATE],
[TypeofMeeting],
[NameofMeeting]
FROM
@meetings
WHERE [TypeofMeeting] = 'private'

/*
Only thing is in "TypeOfMeeting" i have the options of

- Group
- Conference Call
- Private

I want to group all the meetings except "Private" so if i have the same Date,TypeOfMeeting,NameofMeeting
for Group or Conference Call they should all be groups for people involved. However if its Private
then it should not be grouped and all the Private meetings should be seperated.

So the expected result should be:

DATE | TypeOfMeeting | NameOfMeeting

20120801 private Review
20120801 private Review
20120801 group Review
20120801 Conference Call Review

How do i ignore private from the group by results?
*/


Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 774
Thanks guys, I didn't even think about using a union which was pretty simple idea. I was trying all other stuff.

In terms of my whole process - it probably is easier running 2 separate scripts. Because its quiet a detailed process and easier to troubleshoot / investigate running it separately.

But going forward if I have an issue like this on a non-complex script then union would be way to go.

Thanks again
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search