Help! Invalid in the select list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help! Invalid in the select list

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2009-12-22 : 11:41:39

Hello all members,
I had this select query

select
c.Custid,
count(s.custid) as 'Total Shipment' ,
min(Pickupdate) as '1st Shipment' ,
max(pickupdate) as 'last Shipment'
from #ShipCust s right join testCustid c on s.custid = c.custid
group by c.custid

Result
107042 0 NULL NULL
107361 156 2009-08-11 00:00:00.000 2009-12-14 00:00:00.000
108793 0 NULL NULL
109456 25 2009-08-06 00:00:00.000 2009-12-21 00:00:00.000

I want to join the customer table to display more info (name from the customer table)
select
c.Custid,
count(s.custid) as 'Total Shipment' ,
min(Pickupdate) as '1st Shipment' ,
max(pickupdate) as 'last Shipment',
cust.name
from #ShipCust s right join testCustid c on s.custid = c.custid
left join customer cust on s.custid = cust.custid
group by c.custid

I got the error messages:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'cust.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please Help!
Thank you

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-22 : 11:55:27

You need to include cust.name in the group by clause.

select 
c.Custid, 
count(s.custid) as 'Total Shipment' ,
min(Pickupdate) as '1st Shipment' , 
max(pickupdate) as 'last Shipment', 
cust.name
from #ShipCust s right join testCustid c on s.custid = c.custid 
left join customer cust on s.custid = cust.custid
group by c.custid,cust.name

nizguy
Starting Member

37 Posts

Posted - 2009-12-22 : 12:15:47

Yes, It worked fine. But its not return the result as i wanted. Please look at the result. I don't understand why name column return NULL instead the name in the customer table. My second question is: if I want to display more columns like Address, city, states zip etc... from the customer table. Do I need to group by all these fields?
i.e. group by c.custid, cust.name, cust.address1, cust.city, cust.state, cust.zip
Is any other ways to do it?

107361 156 2009-08-11 00:00:00.000 2009-12-14 00:00:00.000 JADE PRODUCTS
108793 0 NULL NULL NULL
109456 25 2009-08-06 00:00:00.000 2009-12-21 00:00:00.000 NOMAD FOOTWEAR
110134 0 NULL NULL NULL
110341 0 NULL NULL NULL

I want the result look like this
107361___156___2009-08-11 00:00:00.000___2009-12-14 00:00:00.000___JADE PRODUCTS
108793___0___NULL___NULL___TRAVELERS CLUB LUGGAGE
109456___25___2009-08-06 00:00:00.000___2009-12-21 00:00:00.000___NOMAD FOOTWEAR
110134___0___NULL___NULL___AMERICAN HATS
110341___0___NULL___NULL___TECH AMERICA

Thank you

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-22 : 12:29:10

Try running this

select 
c.Custid, 
count(s.custid) as 'Total Shipment' ,
min(Pickupdate) as '1st Shipment' , 
max(pickupdate) as 'last Shipment', 
cust.name
from testCustid c 
inner join customer cust on c.custid = cust.custid
left join #ShipCust s on s.custid = c.custid 
group by c.custid,cust.name

nizguy
Starting Member

37 Posts

Posted - 2009-12-22 : 13:03:55

It worked great. thank you
Last question, If I want to select more column from the customer table, this is what i got. Is there other ways to do it instead of keep adding columns to group by ...

select
c.Custid,
count(s.custid) as 'Total Shipment' ,
min(Pickupdate) as '1st Shipment' ,
max(pickupdate) as 'last Shipment',
cust.name, cust.address1, city, state, zip
from testCustid c
inner join customer cust on c.custid = cust.custid
left join #ShipCust s on s.custid = c.custid
group by c.custid,cust.name, cust.address1, city, state, zip

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-22 : 13:08:51

There could be...Try this..

select 
c.Custid, 
s.[Total Shipment] ,
s.[1st Shipment] , 
s.[last Shipment], 
cust.name, 
cust.address1, 
cust.city, 
cust.state, 
cust.zip
from testCustid c 
inner join customer cust on c.custid = cust.custid
left join (select custid, count(s.custid) as [Total Shipment],min(Pickupdate) as [1st Shipment],max(pickupdate) as [last Shipment] from #ShipCust group by custid) s on s.custid = c.custid

nizguy
Starting Member

37 Posts

Posted - 2009-12-22 : 14:06:29

I tried and I got this message. I can't figure out why?

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 's' does not match with a table name or alias name used in the query.

nizguy
Starting Member

37 Posts

Posted - 2009-12-22 : 14:18:55

I found it. I removed the s. from Count(s.custid)

Worked great! Thank you vijayisonly

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-22 : 15:03:57

Np. You're welcome.

quote:
Originally posted by nizguy

I found it. I removed the s. from Count(s.custid)

Worked great! Thank you vijayisonly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources