Error in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error in query

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-31 : 09:42:29

Hi all,

I need all fields from users table but it is giving following error:

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

select u.*,sum(subtotal) Totalamount,orders.customerid from orderdetails inner join orders
on orders.orderID = orderdetails.orderid
inner join wholesalers w
on w.userid = orders.customerid
inner join users u
on u.userid = w.userid
--and orders.customerid = 1
and w.isvalidationrequired = 1
where orders.datecreated
between dateadd(day, -20, getdate()) and getdate()
group by orders.customerid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-31 : 09:55:00

[code]
select ~~u.*,~~ sum(subtotal) Totalamount,orders.customerid
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-31 : 10:37:16

I can't omit U.* from above query because i need all fields from users table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-31 : 13:05:10

Then you have to give table structure, sample data and wanted output.

No, you're never too old to Yak'n'Roll if you're too young to die.

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-31 : 13:09:26

I have an order table and order details table which has order detail id pk and order id as primary key and subtotal of item. I want to get only those customers from orders table whose orders in last one week have amount greater than 50. order table has customer id. orders table has status field. only complete orders in last 7 days amount need to be added in sum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 14:05:54

something like:-


SELECT o.*
FROM Orders o
INNER JOIN (SELECT Orderid,SUM(subtotal) as TotalOrderValue
            FROM OrderDetail
            GROUP BY Orderid
            HAVING SUM(subtotal)>50) od
ON od.Orderid=o.Orderid
WHERE o.Status='Completed'
AND o.OrderDate >= DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)
AND o.OrderDate < DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-31 : 14:22:56

hi visakh16,

how can i combine above query with customer table. ordertable has customerid. do i need above query as inner query ? also do i need a group by ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 14:33:28

[code]
SELECT o.*,c.*
FROM Orders o
INNER JOIN Customers c
ON c.CustomerID=o.CustomerID
INNER JOIN (SELECT Orderid,SUM(subtotal) as TotalOrderValue
FROM OrderDetail
GROUP BY Orderid
HAVING SUM(subtotal)>50) od
ON od.Orderid=o.Orderid
WHERE o.Status='Completed'
AND o.OrderDate >= DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)
AND o.OrderDate < DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources