Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select statement with Group BY Expand / Collapse
Author
Message
Posted Thursday, June 24, 2010 1:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 5:26 AM
Points: 29, 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
Post #942260
Posted Thursday, June 24, 2010 1:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 5,014, Visits: 10,515
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #942270
Posted Thursday, June 24, 2010 1:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 5:26 AM
Points: 29, 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
Post #942273
Posted Thursday, June 24, 2010 1:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 5,014, Visits: 10,515
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #942278
Posted Thursday, June 24, 2010 3:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #942323
Posted Thursday, June 24, 2010 4:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 11,192, Visits: 11,096
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #942349
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse