CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
Karlx Swanovski
Karlx Swanovski

Reputation: 2989

Column is invalid in select

How I will properly do this:

Customer

CusID, CusLname, CusFname, CusMname, CusAddress, CusEmailAdd

Order 

OrderID,  Order,   CusID

SQL

Select  Count(OrderID), o.CusID, CusLname, CusFname, CusMname, CusAddress, CusEmailAdd
From Customer c 
Inner join
Order o
On c.CusID = o.CusID
Group By o.CusID

Error Column 'CusLname, CusFname, CusMname, CusAddress, CusEmailAdd' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why do I need to add those columns in the Group By Clause? What will I do to select those columns without having them in the Group By because I only want to group them by CusID?

Upvotes: 0

Views: 102

Answers (4)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

I also find first answer to be the best, but in SQL2005 CTEs came into existence and they can help obtain better readability:

;with order_cte as (
    select count(OrderID) cnt, CusID
    from Order o 
    group by CusID
)
select cnt, c.CusID, CusLname, CusFname, CusMname, CusAddress, CusEmailAdd
from Customer c 
   join order_cte o on c.CusID = o.CusID
order by c.CusID

Upvotes: 0

pkuderov
pkuderov

Reputation: 3571

A little bit different version (from that Gordon Linoff suggests):

select
     cnt, c.CusID, CusLname, CusFname, CusMname, CusAddress, CusEmailAdd
from Customer c 
join (
    select  
        count(OrderID) cnt, CusID
    from Order o 
    group by
        CusID
) o on
    c.CusID = o.CusID
order by
    c.CusID // ordering by OrderID and Order is out of logic :)

Upvotes: 1

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

Why do I need to add those columns in the Group By Clause?

Because you are saying you need to group the results and give one result per ID. This leaves you with two choices.

1) Getting the count with all details at the customer level, which is what you would want in your example.

Select  Count(OrderID), o.CusID, CusLname, CusFname, CusMname, CusAddress, CusEmailAdd
From Customer c 
Inner join
Order o
On c.CusID = o.CusID
Group By o.CusID, CusLname, CusFname, CusMname, CusAddress, CusEmailAdd;

2) Or in case the other columns are some numbers (say salaries in a department) or so, every column not in the group by should have an aggregate function.

select dept_id, sum(salary), max(salary), min(salary)
from dept
group by dept_id;

When you want one row per your grouped column, you need to specify how to aggregate the other columns.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270191

Just include them in the group by clause:

Select  Count(OrderID), o.CusID, CusLname, CusFname, CusMname, CusAddress, CusEmailAdd
From Customer c 
Inner join
Order o
On c.CusID = o.CusID
Group By o.CusID, CusLname, CusFname, CusMname, CusAddress, CusEmailAdd;

Upvotes: 0

Related Questions