GracefBase
asked on
SQL query for a view - problem with grouping
Hello there experts,
Trying to amend a view so I can show the total of small cartons as well as the total cartons for a shipment.
I'm getting the error
"Column 'AutoPick.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
If I add this to the GROUP BY clause instead of getting one line of data I get one for each of these Autopick.Statuses - here is the query.
select m.mrid, .exdate1, invAddr.companyid as invoiceAddr,
invAddr.code, m.sourceid, invAddr.Dropseqnumber, dest.companyid as destid, dest.pc,
case (dest.pcgdesc)
when '' then (case m.TimeExp when '00:00:00' then
'false' else 'true' End )
else 'true' END
as timed,
case (dest.DropSeqNumber)
when 1 then 'true'
else 'false' END
as tailLift,
sum(autopick.Amount)
as ctns,
(Select COUNT(OriginalPID) from Pallets WHERE SType = 6 AND Autopick.Status = Pallets.Status
GROUP by Pallets.OriginalPID) as SmlCtns
FROM m
INNER JOIN Companies source on m.SourceId = source.CompanyId
INNER JOIN companies invAddr on (dbo.invoiceTo(m.mrid) = invAddr.CompanyId)
INNER JOIN Companies dest on mrheader.DestId = dest.CompanyId
INNER JOIN MrLines on m.MrId=MrLines.MrId
INNER JOIN AutoPick on mrlines.MrId = autopick.MrIdOut and mrlines.MrLineNo=autopick. MrLineNoOu t
WHERE m.allowcharge=1
AND dbo.invoiceTo(mrheader.mri d)=invAddr .companyid
AND mrtype=2
and locateit.dbo.invoiceto(mrh eader.mrid ) is not null
AND invAddr.Code <> 'CKF'
AND DestId not in (1342, 7308)
AND ExDate1 > '2011-05-31 00:00:00'
group by m.mrid, m.ExDate1, m.TimeExp, m.sourceid,
invAddr.companyid, invAddr.code, invAddr.Dropseqnumber, dest.companyid, dest.pc, dest.pcgdesc,
dest.DropSeqNumber
Thanks in advance for any help.
Trying to amend a view so I can show the total of small cartons as well as the total cartons for a shipment.
I'm getting the error
"Column 'AutoPick.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
If I add this to the GROUP BY clause instead of getting one line of data I get one for each of these Autopick.Statuses - here is the query.
select m.mrid, .exdate1, invAddr.companyid as invoiceAddr,
invAddr.code, m.sourceid, invAddr.Dropseqnumber, dest.companyid as destid, dest.pc,
case (dest.pcgdesc)
when '' then (case m.TimeExp when '00:00:00' then
'false' else 'true' End )
else 'true' END
as timed,
case (dest.DropSeqNumber)
when 1 then 'true'
else 'false' END
as tailLift,
sum(autopick.Amount)
as ctns,
(Select COUNT(OriginalPID) from Pallets WHERE SType = 6 AND Autopick.Status = Pallets.Status
GROUP by Pallets.OriginalPID) as SmlCtns
FROM m
INNER JOIN Companies source on m.SourceId = source.CompanyId
INNER JOIN companies invAddr on (dbo.invoiceTo(m.mrid) = invAddr.CompanyId)
INNER JOIN Companies dest on mrheader.DestId = dest.CompanyId
INNER JOIN MrLines on m.MrId=MrLines.MrId
INNER JOIN AutoPick on mrlines.MrId = autopick.MrIdOut and mrlines.MrLineNo=autopick.
WHERE m.allowcharge=1
AND dbo.invoiceTo(mrheader.mri
AND mrtype=2
and locateit.dbo.invoiceto(mrh
AND invAddr.Code <> 'CKF'
AND DestId not in (1342, 7308)
AND ExDate1 > '2011-05-31 00:00:00'
group by m.mrid, m.ExDate1, m.TimeExp, m.sourceid,
invAddr.companyid, invAddr.code, invAddr.Dropseqnumber, dest.companyid, dest.pc, dest.pcgdesc,
dest.DropSeqNumber
Thanks in advance for any help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi, sorry but I cannot get the carton query now to give the correct result. Please could you see if you can work out a subquery or running total for me. The join I have added to join the Pallet table is
LEFT OUTER Join Pallets on autopick.Status = pallets.status
Thanks a lot
LEFT OUTER Join Pallets on autopick.Status = pallets.status
Thanks a lot
ASKER
Thanks for your help. I managed to make a subquery to resolve the other issue but the main issue was answered, thanks
ASKER
Thanks a lot for that. I did that, and I added the pallets autopick join to the main from clause and changed the sum to count for the cartons and this now works.