Problem with Sql Server Group By clause

Question

0.00/5 (No votes)

See more:

SQL

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 27-Feb-14 19:58pm

DevilsCod

Updated 28-Feb-14 16:31pm

v3

Add a Solution

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

Add a Solution

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

Tejas Vaishnav · Answer 1 · 2014-02-27T21:25:00

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...

SQL

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

Problem with Sql Server Group By clause

1 solution

Solution 1

Add your solution here

Preview 0

Problem with Sql Server Group By clause

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us