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

I have a query written to populate 3 columns Date,Total Orders,Amount in a table.But now I want to add few more columns in the query to elaborate the table with 6 more columns.But When I try to add column in the SELECT LIST it is throwing an error:
"invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Here goes my query:

I  want to add columns like FirstName, lastname, shipping from the orders table.


Thanks!


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,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)
 Union    
    SELECT 'Grand Total' as Date, 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

Open in new window

onebite2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
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

Open in new window

0
mfsamuelCommented:
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?
0
mfsamuelCommented:
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

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

onebite2Author Commented:
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!
0
Faiga DiegelSr Database EngineerCommented:
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...
0
Faiga DiegelSr Database EngineerCommented:
or change the MIN() function to MAX().
0
mfsamuelCommented:
post the qurey you are trying to run.  the one above is the original without the 3 fields you need.
0
onebite2Author Commented:
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

Open in new window

0
mfsamuelCommented:
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

Open in new window

0
onebite2Author Commented:
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!
0
mfsamuelCommented:
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

Open in new window

0
mfsamuelCommented:
sorry, you will need to replace the sql at the bottom b/c the function is on the same script.
0
onebite2Author Commented:
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

Open in new window

0
mfsamuelCommented:
Try this.  The Union needs the same number of columns and data types.  I had provided this above, but there was an error with the group by that faiga16 pointed out.
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
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('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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
onebite2Author Commented:
Hey mfsamuel,

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

Cindy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.