CODEWITHSUNDEEP
mysqlsql-server
eto_donna
eto_donna

Reputation: 61

Query with multiple COUNTS() and a GROUP BY

I have a query that returns all the ship parts, factory parts, the date each was entered, and by whom with their respective email. But I need the totall count of all the ship parts and factory parts each person has entered so I added the COUNT functions you see below but I keep gettng this error "Each GROUP BY expression must contain at least one column that is not an outer reference." Any ideas?

select Distinct cc.FirstName+' '+cc.LastName AS 'Name', UEmail AS 'Email', sp.SEntered AS 'Date Entered', COUNT(sp.SHIPID) AS 'Shipments',   COUNT(pn.PNumber) AS 'Factory Parts'
from R_USERS_Detail U
join R_SHIPMENTS sp on sp.UID=U.UID
join R_SHIPMENTOrders sd on sd.SHIPID=sp.SHIPID
join R_PARTOrders pn on pn.PNumberID=sd.PNumberID
join R_Clients c on c.COClientID=sp.COClientID AND c.COClientAcronym LIKE '%AME%'
join R_Contacts cc on cc.COContactID=u.COContactID
group by u.UEmail, sp.SEntered, 'Name'

Upvotes: 1

Views: 26

Answers (1)

M.Ali
M.Ali

Reputation: 69494

You have to use the exact expression in the group by claus , not the alias. 

select cc.FirstName+' '+cc.LastName AS 'Name'
     , UEmail AS 'Email'
     , sp.SEntered AS 'Date Entered'
     , COUNT(sp.SHIPID) AS 'Shipments'
     , COUNT(pn.PNumber) AS 'Factory Parts'
from R_USERS_Detail U
join R_SHIPMENTS sp on sp.UID=U.UID
join R_SHIPMENTOrders sd on sd.SHIPID=sp.SHIPID
join R_PARTOrders pn on pn.PNumberID=sd.PNumberID
join R_Clients c on c.COClientID=sp.COClientID AND c.COClientAcronym LIKE '%AME%'
join R_Contacts cc on cc.COContactID=u.COContactID
group by u.UEmail, sp.SEntered, cc.FirstName+' '+cc.LastName

Upvotes: 2

Related Questions