Getting data from multiple tables in my SQL Server 2012

Question

I have this query

SELECT 
    sa.MSISDN, 
    MIN(sa.DateRegistered) AS firstReg, 
    s.DateRegistered AS currentReg,  
    MAX(sa.DateRegistered) AS previousReg 
FROM 
    sms.dbo.SubscriptionsArchive sa 
INNER JOIN 
    sms.dbo.Subscriptions s ON sa.MSISDN = s.MSISDN 
GROUP BY 
    sa.MSISDN

And its wrong as s.DateRegistered is not used in aggregate function and it is not used in group by.

How do I correct this query?

Yosef Weiner · Accepted Answer · 2015-07-06 05:59:38Z

2

Add s.DateRegistered to your group-by clause

answered Jul 6, 2015 at 5:59

Yosef Weiner

5,7811 gold badge27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

suman Over a year ago

Yea that works but is this the correct way to get the result ? Is there a better way?

suman Over a year ago

Ok then thanks and one more thing, this query gives result but i want to make sure this is the correct way to do

SELECT sr.ID, sr.ContentName AS service, sa.MSISDN, MIN(sa.DateRegistered) AS firstReg,  s.DateRegistered AS currentReg , MAX(sa.DateRegistered) AS previousReg FROM sms.dbo.SubscriptionsArchive sa    INNER JOIN (SELECT DateRegistered, MSISDN, ServiceID from sms.dbo.Subscriptions) s ON sa.MSISDN = s.MSISDN    INNER JOIN (SELECT * FROM sms.dbo.Services) sr ON sr.Id = s.ServiceID GROUP BY sa.MSISDN, s.DateRegistered, sr.ID, sr.ContentName

Yosef Weiner Over a year ago

Any column that you reference in the query must be in the group-by clause unless they are inside aggregate functions

Yosef Weiner Over a year ago

Your best bet is to run it and see

suman Over a year ago

Yea it works. I just want to make sure this way is a proper way.

Collectives™ on Stack Overflow

Getting data from multiple tables in my SQL Server 2012

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related