Select statement with Group BY

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Select statement with Group BY

Select statement with Group BY

Rate Topic

Display Mode

Topic Options

Author

Message

Vinay Theethira

Posted Thursday, June 24, 2010 1:13 AM

SSC 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

spaghettidba

Posted Thursday, June 24, 2010 1:33 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, June 14, 2013 7:05 AM
Points: 4,804, Visits: 8,090

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?

Get your two-cent-answer quickly
The Spaghetti DBA

Post #942270

Vinay Theethira

Posted Thursday, June 24, 2010 1:41 AM

SSC 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

spaghettidba

Posted Thursday, June 24, 2010 1:52 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, June 14, 2013 7:05 AM
Points: 4,804, Visits: 8,090

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.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #942278

Eugene Elutin

Posted Thursday, June 24, 2010 3:49 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 2:27 AM
Points: 2,596, Visits: 4,505

Vinay Theethira (6/24/2010)

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #942323

Paul White

Posted Thursday, June 24, 2010 4:31 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 10,990, Visits: 10,572

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 »

Permissions