Sql query error: invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Try this

SELECT Convert(Varchar(15), l.[date],101)as Date,COUNT(o.OrderID ) AS TotalOrders,
 ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, Min(FirstName) FirstName, Min(lastname) lastname, Min(shipping) shipping, 1 as OrderByCol 
FROM dbo.CreateDateList( @DateFrom , @DateTo ) l
 LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) 
 WHERE StoreID=@StoreID 
 GROUP BY Convert(Datetime,l.[date],101)
 Union    
    SELECT 'Grand Total' as Date, NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, NUll, Null, Null, 2 as OrderByCol 
FROM dbo.CreateDateList(@DateFrom ,@DateTo ) l 
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))  
WHERE StoreID= @StoreID
   Order by Date

Select allOpen in new window

The above query will return the min first, last, and order.  They may not even corespond to each other.

What are you trying to display?

try this...

fUNCTION:
create function dbo.CreateDateList(@start datetime, @end datetime) returns @t table ( [date] datetime ) as begin
  if @start is null or @end is null 
    return
 
  if @start > @end 
    return
 
  set @start = convert(datetime, convert(varchar(10), @start, 120), 120)
  set @end = convert(datetime, convert(varchar(10), @end, 120), 120)
 
  while @start < @end
  begin
    insert into @t ( [date] ) values (@start)
    set @start = dateadd(day, 1, @start)
  end
 
  return
end
 
 
 
 
---QUERY
 
SELECT Convert(Varchar(15), l.[date],101) as Date,o.FirstName, o.LastNAme, o.shipping,COUNT(o.OrderID ) AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol 
FROM dbo.CreateDateList( @DateFrom , @DateTo ) l
 LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) 
 WHERE StoreID=@StoreID 
 GROUP BY Convert(Datetime,l.[date],101),o.FirstName, o.LastNAme, o.shipping
 Union    
SELECT 'Grand Total' as Date, NULL,NULL,NULL,NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, 2 as OrderByCol 
FROM dbo.CreateDateList(@DateFrom ,@DateTo ) l 
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))  
WHERE StoreID= @StoreID
   Order by Date

Select allOpen in new window

I got the same error again,after making changes.

Column 'orders.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Can you tell me what could we do next?

Thanks!

your GROUP BY clause before the UNION

GROUP BY Convert(Datetime,l.[date],101) should be  GROUP BY Convert(Varchar(15), l.[date],101)

try it...

or change the MIN() function to MAX().

post the qurey you are trying to run.  the one above is the original without the 3 fields you need.

I used my function and query to display a table with Date ,Total No of orders on that date ,Total amount on that day and Grand total amount of all days .

But now I want to include additional columns like First name,last name,shipping form orders table and I am getting this error.I used the following query which u agve me.


Column 'orders.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.



Help me to find a work around.

Thanks!

SELECT Convert(Varchar(15), l.[date],101) as Date,o.SFirstName, o.SLastNAme, o.shipped,COUNT(o.OrderID ) AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol 
--  SELECT Convert(Varchar(15), l.[date],101)as Date,o.SFirstName,COUNT(o.OrderID ) AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol
 FROM dbo.CreateDateList( '1/21/2007' , '1/22/2008' ) l 
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))
  WHERE StoreID='SAM'
GROUP BY Convert(Varchar(15), l.[date],101)  
--GROUP BY Convert(Datetime,l.[date],101)
 Union       
 SELECT 'Grand Total' as Date,o.SFirstName, NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, 2 as OrderByCol
 FROM dbo.CreateDateList('1/21/2007' , '1/22/2008' ) l 
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))    
WHERE StoreID='SAM'  
 Order by Date

Select allOpen in new window

need to add it to the group by.

SELECT Convert(Varchar(15), l.[date],101) as Date,o.SFirstName, o.SLastNAme, o.shipped,COUNT(o.OrderID ) AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol 
--  SELECT Convert(Varchar(15), l.[date],101)as Date,o.SFirstName,COUNT(o.OrderID ) AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol
 FROM dbo.CreateDateList( '1/21/2007' , '1/22/2008' ) l 
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))
  WHERE StoreID='SAM'
GROUP BY Convert(Varchar(15), l.[date],101),o.SFirstName, o.SLastNAme, o.shipped
--GROUP BY Convert(Datetime,l.[date],101)
 Union       
 SELECT 'Grand Total' as Date,o.SFirstName, NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, 2 as OrderByCol
 FROM dbo.CreateDateList('1/21/2007' , '1/22/2008' ) l 
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))    
WHERE StoreID='SAM'  
 Order by Date

Select allOpen in new window

I did the same thing added  it to the GroupBy ...what you posted but no luck its still showing the same error.......



Can we find any other work around?

Thanks!

are you using this code or manually changing another file to have the same changes?  can you try running this.

do not make changes just run this query, can be in it's own window.

SELECT Convert(Varchar(15), l.[date],101) as Date,o.SFirstName, o.SLastNAme, o.shipped,COUNT(o.OrderID ) AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol 
FROM dbo.CreateDateList( '1/21/2007' , '1/22/2008' ) l 
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))
WHERE StoreID='SAM'
GROUP BY Convert(Varchar(15), l.[date],101) as Date,o.SFirstName, o.SLastNAme, o.shipped

Select allOpen in new window

sorry, you will need to replace the sql at the bottom b/c the function is on the same script.

The first part of the sql is running and giving me correct output.....its giving me all the columns.......but when the second part is attached its throwing errors like that.


Second part is added to get the GrandTotal for the total  amount field at the bottom....what can we do so that even this works?Do you have any work around?

I appreciate ur help!

 
SELECT Convert(Varchar(15), l.[date],101) as Date,o.SFirstName, o.SLastNAme, o.shipped,COUNT(o.OrderID ) AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol 
--  SELECT Convert(Varchar(15), l.[date],101)as Date,o.SFirstName,COUNT(o.OrderID ) AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol
 FROM dbo.CreateDateList( '1/21/2007' , '1/22/2008' ) l 
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))
  WHERE StoreID='sam'
GROUP BY Convert(Varchar(15), l.[date],101),o.SFirstName, o.SLastNAme, o.shipped

Select allOpen in new window

Hey mfsamuel,

I could get the problem solved ...Thanks for ur help I appreciate!

Cindy