SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select statement with Group BY


Select statement with Group BY

Author
Message
Vinay Theethira
Vinay Theethira
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 105
Hi,

if we are trying to add the several columns in select statement and only one condition in group by.

how can we do that ??
Example:
select ContactTracking.Contact_ID,ContactTracking.HcnID
FROM
ContactTracking ContactTracking
LEFT OUTER JOIN HRA hra ON hra.HcnID = ContactTracking.HcnID
WHERE
ContactTracking.contact_date>'10-jan-2009'
AND ContactTracking.Call_end_time is not null
AND ContactTracking.HcnID NOT IN (Select HcnID FROM TestIDs)
AND Contact_ID in ('122734','122738')
GROUP BY Contact_ID

Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9824 Visits: 13350
No, it can't be done.
Depending on the aggregate you're using, you could rewrite the query to achieve the same thing in a different way.
Problem is I don't see any aggregate in you query.
Why are you grouping if you don't use aggregates?
Can you explain the requirement a bit further?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Vinay Theethira
Vinay Theethira
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 105
Here is my SQL Query, This query should not fetch duplicate contactID(i.e Same ContactID).but when i execute the query i get duplicated ContactID. Could anyone tell me what is wrong with my Query,

Here is my query
select ContactTracking.Contact_ID
FROM
ContactTracking ContactTracking
LEFT OUTER JOIN Issues Issues ON ContactTracking.Issue_ID = Issues.Issue_ID
LEFT OUTER JOIN HRA hra ON hra.HcnID = ContactTracking.HcnID
INNER JOIN HPS_COMMON.dbo.wrk_user wrk_User ON ContactTracking.UserID = wrk_User.id_User
INNER JOIN HPS_COMMON.dbo.def_Usertype def_Usertype ON wrk_User.id_UserType = def_Usertype.id_UserType
WHERE
ContactTracking.contact_date>'10-jan-2009'
AND ContactTracking.Call_end_time is not null
AND Contact_ID in ('122734','122738')

And here is the ContactId result i get,
122734
122734
122738
122738

Expected Result is,
122734
122738

So i thought Group By will solve the iss
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9824 Visits: 13350
Probably cardinality changes in your joins to other tables. I can't tell from here.
Try using DISTINCT in your query (it goes immediately after SELECT).

I would suggest you to check the relationships between your tables and find out if duplicating rows in joins is permitted by the database schema or is due to a wrong query.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4970 Visits: 5478
Vinay Theethira (6/24/2010)
Here is my SQL Query, This query should not fetch duplicate contactID(i.e Same ContactID).but when i execute the query i get duplicated ContactID. Could anyone tell me what is wrong with my Query,

Here is my query
select ContactTracking.Contact_ID
FROM
ContactTracking ContactTracking
LEFT OUTER JOIN Issues Issues ON ContactTracking.Issue_ID = Issues.Issue_ID
LEFT OUTER JOIN HRA hra ON hra.HcnID = ContactTracking.HcnID
INNER JOIN HPS_COMMON.dbo.wrk_user wrk_User ON ContactTracking.UserID = wrk_User.id_User
INNER JOIN HPS_COMMON.dbo.def_Usertype def_Usertype ON wrk_User.id_UserType = def_Usertype.id_UserType
WHERE
ContactTracking.contact_date>'10-jan-2009'
AND ContactTracking.Call_end_time is not null
AND Contact_ID in ('122734','122738')

And here is the ContactId result i get,
122734
122734
122738
122738

Expected Result is,
122734
122738

So i thought Group By will solve the iss


Where exactly in your query you have put "GROUP BY ContactTracking.ContactId", I cannot see it, must be very well hidden. :-D

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15614 Visits: 11355

SELECT CT.Contact_ID
FROM ContactTracking CT
WHERE CT.contact_date > '20090110'
AND CT.Call_end_time IS NOT NULL
AND Contact_ID IN ('122734', '122738')
AND EXISTS
(
SELECT *
FROM HPS_COMMON.dbo.wrk_user WU
JOIN HPS_COMMON.dbo.def_Usertype UT
ON UT.id_UserType = WU.id_UserType
WHERE WU.id_User = CT.UserID
);





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
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