Click here to Skip to main content
14,766,249 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
WITH CTE AS (
WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY di.recno ),
		si.ShipmentNo as ManiFest,
		t.TruckNo, 
  		j.Route,
		j.Ordernumber as Invoice,
		w.Name,di.Latitude as dLatitude ,di.Longitude as dLongitude,di.DeliveryDate,
		di.DeliveryTime ,w.Latitude ,w.Longitude ,'' as DistanceBy
		

from tbl_pms_DeliveryInfo di
Inner Join tbl_pms_JobOrders j on j.JobID = di.JobID
Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID = j.JobID
Inner Join tbl_pms_Trucks t on t.TruckID = jt.TruckID  
Inner Join tbl_pms_Warehouses w on w.WarehouseID = j.WarehouseID 
Inner Join tbl_pms_ShipmentInfo si on si.ShipmentID = jt.ShipmentID 
where t.TruckID != 312
)

select cte.Route ,
		COUNT(distinct CTE.ManiFest)as TotalManiFest,
		COUNT(CTE.Invoice) as TotalInvoice,
		CTE.TruckNo,
		CTE.Name,
		CTE.DeliveryDate
		
		--dbo.Distance(CTE.dLatitude,CTE.dLongitude,CTE.Latitude,CTE.Longitude) as DISTANCEWH,   
		--dbo.Distance(CTE.dLatitude,CTE.dLongitude,prev.dLatitude,prev.dLongitude) as DISTANCEATPOINT 
		
from CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
Group by CTE.Route ,CTE.TruckNo ,CTE.Name ,CTE.DeliveryDate,
--CTE.dLatitude,CTE.dLongitude,CTE.Latitude,CTE.Longitude  



In Above Sql query i have to Count two columns and also calculate Distance.

i am getting errors by group by clause

Column 'CTE.dLatitude' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.(lly dlongitude,lat,long)

i dont want latitude and longitute in groupby
Posted
Updated 28-Feb-14 17:31pm
v3
Comments
Tejas Vaishnav 28-Feb-14 2:04am
   
try to add CTE.dLatitude,CTE.dLongitude,CTE.Latitude,CTE.Longitude columns in your group by cause and check..?
King Fisher 28-Feb-14 2:48am
   
mr.Tejas ,he said that " i dont want latitude and longitute in groupby "
King Fisher 28-Feb-14 2:49am
   
use aggregate function for this CTE.dLatitude,CTE.dLongitude,CTE.Latitude,CTE.Longitude
Tejas Vaishnav 28-Feb-14 3:19am
   
have you ever run any group by cause, if so then you can get what i am trying to tell him, if you are using any column in your select query which is having group by cause, then you must need to add that column in your group by cause, or if you use any aggregate function then it is fine, but here Krishna is trying to calculate distance using user defined function so and passes lat long information to it, so its used in select statement, that's why he need to add those column in group by cause.
DevilsCod 28-Feb-14 22:25pm
   
Dear Tejas, Thanks for ur reply , here in this query i know the lat,long are to add group by caluse , but my problem is i want to group only route and Truckno. Please give me another solution to do this .. Thank All

1 solution

you are already calculate distance in your CTE, so why are you calculate it again in your select statement next to CTE, just use it from CTE like this...


WITH CTE AS (
    SELECT
        rownum = ROW_NUMBER() OVER (ORDER BY di.recno ),
        si.ShipmentNo as ManiFest,
        t.TruckNo, 
        j.Route,
        j.Ordernumber as Invoice,
        w.Name,di.Latitude as dLatitude ,di.Longitude as dLongitude,di.DeliveryDate,
        di.DeliveryTime ,w.Latitude ,w.Longitude ,'' as DistanceBy,
        dbo.Distance(w.Latitude ,w.Longitude, di.Latitude ,di.Longitude) as Distance 
    from tbl_pms_DeliveryInfo di
    Inner Join tbl_pms_JobOrders j on j.JobID = di.JobID
    Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID = j.JobID
    Inner Join tbl_pms_Trucks t on t.TruckID = jt.TruckID  
    Inner Join tbl_pms_Warehouses w on w.WarehouseID = j.WarehouseID 
    Inner Join tbl_pms_ShipmentInfo si on si.ShipmentID = jt.ShipmentID 
    where t.TruckID != 312 and
    t.TruckNo   = 'WFN 2976' and j.Planneddate between '20131003' and '20140103'
)
 
SELECT  
      CTE.Route
    , COUNT(distinct CTE.ManiFest)as TotalManiFest
    , COUNT(CTE.Invoice) as TotalInvoice
    , CTE.TruckNo
    , CTE.Name
    , CTE.DeliveryDate
    , CTE.Distance as DISTANCEWH
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
GROUP BY 
      CTE.Route 
    , CTE.TruckNo 
    , CTE.Name 
    , CTE.DeliveryDate
    , CTE.Distance
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900