Select statement with Group BY

Question

Post reply

Select statement with Group BY

Vinay Theethira

Right there with Babe

Points: 775
More actions
June 24, 2010 at 1:13 am

#387212

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 JOINHRA 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

Vinay Theethira Right there with Babe Points: 775 More actions · Answer 2

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

122738

Expected Result is,

122734

122738

So i thought Group By will solve the iss

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

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. 😀

_____________________________________________
"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[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 5

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
SQLkiwi blog
@SQL_Kiwi