Group By

I have one table that I have turned into 2 tables.  I have assigned aliases to the tables.  So I have t1 and t2.  There is one field in the tables that I have combined in a case statement to make a comparison.  The query executes successfully, but rows are dropped periodically.  How can I list both column names in the group by?   I believe that this is the reason why I am dropping rows.  The column name is time.  So it would be t1.time and t2.time.  
daintysallyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
If those have timestamp values they may not be exactly equal if using that to join so that may be one problem; however, this is how you would put multiple columsn in group by:

SELECT ...
FROM t1 JOIN t2 ...
GROUP BY t1.time, t2.time
0
jamesguCommented:
select t1.time , t2.time, count(...)
from t1, t2
where t1.id = t2.id ...
group by  t1.time = t2.time

if it doesn't work, please attach your query here
0
daintysallyAuthor Commented:
They do have timestamp values.  Please see a part of my case statement and the grouping that follows it in the code snippet
HEN DATEPART(DW,AL2.AU_TIME)NOT IN (1,7)
AND DATEDIFF(ms,AL2.AU_TIME,AL1.AU_TIME)> 0
AND DATEDIFF(dd,AL2.AU_TIME,AL1.AU_TIME)> 0
AND DATEDIFF(hh,AL2.AU_TIME,AL1.AU_TIME)> 0
AND DATEDIFF(mi,AL2.AU_TIME,AL1.AU_TIME)> 0
AND DATEDIFF(ss,AL2.AU_TIME,AL1.AU_TIME)> 0 THEN DATEDIFF(ms,AL2.AU_TIME,AL1.AU_TIME)
END) 
AS "DAYS_IN_STATUS"
 
 
....
 
Group by BUG.BG_BUG_ID, BUG.BG_PLANNED_CLOSING_VER, BUG.BG_STATUS, BUG.BG_SEVERITY,BUG.BG_USER_14, BUG.BG_RESPONSIBLE,BUG.BG_USER_62, BUG.BG_DETECTION_DATE,
BUG.BG_USER_18,BUG.BG_USER_07,AL2.AU_TIME,AL2.AU_USER,AP2.AP_FIELD_NAME,AP2.AP_OLD_VALUE,AP2.AP_NEW_VALUE
Order By BUG.BG_BUG_ID,BUG.BG_USER_14

Open in new window

0
daintysallyAuthor Commented:
The column that I am referring to, is AL1.AU_TIME AND AL2.AU_TIME
0
Kevin CrossChief Technology OfficerCommented:
You could just add both in the GROUP BY statement as shown, but what happens most times in a case like this is you may end up needing the formula (DATEDIFF) in the group by since that is the value that needs to be grouped in common and not the individual time values.

i.e. DATEDIFF(ms,AL2.AU_TIME,AL1.AU_TIME)

if value 2000ms, then for any two dates that are 2000ms apart they will be grouped together.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.