SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 AdventureWorks2012 query: get name and ID of cust
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gpspreet
Starting Member

8 Posts

Posted - 03/30/2013 :  00:40:04  Show Profile  Reply with Quote
AdventureWorks2012:
Identify the customers who have orders with total order value of more than $100,000.00 in the year 2007.
Show: customer ID, Customer Name of such orders
Order by: customer ID

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/30/2013 :  01:52:49  Show Profile  Reply with Quote
Can we see what you tried yet. This is a straightforward assignment question so unless you tried yourself we wont provide any spoonfed answers.

We can give a hint though....Lookup for GROUP BY clause in books online

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gpspreet
Starting Member

8 Posts

Posted - 03/30/2013 :  03:42:03  Show Profile  Reply with Quote
Sure....

I tried Below code. I am unaable to print the Customer Names.

SELECT SOH.CustomerID, TotalDue= sum(SOH.TotalDue)
FROM Sales.SalesOrderHeader SOH
Inner JOIN Sales.Customer CUS ON SOH.CustomerID = CUS.CustomerID
LEFT OUTER JOIN Person.BusinessEntityContact BEC ON CUS.PersonID =BEC.PersonID
LEFT OUTER JOIN Person.Person PER ON BEC.BusinessEntityID = PER.BusinessEntityID
where year(SOH.OrderDate) = '2007' and TotalDue > 100000 group by SOH.CustomerID ORDER BY SOH.CustomerID;

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/30/2013 :  03:45:27  Show Profile  Reply with Quote
you've not listed customernames in select. Then how would it print them. Add Customername field in SELECT and GROUP BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gpspreet
Starting Member

8 Posts

Posted - 03/30/2013 :  03:56:10  Show Profile  Reply with Quote
Thanks For looking into it...

It throws the below error for first,second and last name

Column 'Person.Person.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/30/2013 :  04:01:47  Show Profile  Reply with Quote
just give attention to what i suggested previously.

To restate

Add name related fields in SELECT and in GROUP BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gpspreet
Starting Member

8 Posts

Posted - 03/30/2013 :  05:04:34  Show Profile  Reply with Quote
I tried but i am not getting it. How can i combine the existing groupby with select...I am not getting it at all.

[SELECT SOH.CustomerID, TotalDue= sum(SOH.TotalDue)
FROM Sales.SalesOrderHeader SOH
Inner JOIN Sales.Customer CUS ON SOH.CustomerID = CUS.CustomerID
Left Outer JOIN Person.BusinessEntityContact BEC ON CUS.PersonID =BEC.PersonID
Left outer JOIN (select p.FirstName,p.LastName, BusinessEntityID from person.person p ) per ON BEC.BusinessEntityID = per.BusinessEntityID
where year(SOH.OrderDate) = '2007' and TotalDue > 100000 group by SOH.CustomerID ORDER BY SOH.CustomerID;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/30/2013 :  13:05:48  Show Profile  Reply with Quote

SELECT SOH.CustomerID,per.LastName,per.firstName, TotalDue= sum(SOH.TotalDue) 
FROM Sales.SalesOrderHeader SOH 
Inner JOIN Sales.Customer CUS ON SOH.CustomerID = CUS.CustomerID
Left Outer JOIN Person.BusinessEntityContact BEC ON CUS.PersonID =BEC.PersonID 
Left outer JOIN (select p.FirstName,p.LastName, BusinessEntityID from person.person p ) per ON BEC.BusinessEntityID = per.BusinessEntityID 
where year(SOH.OrderDate) = '2007' and TotalDue > 100000 group by SOH.CustomerID,per.LastName,per.firstName ORDER BY SOH.CustomerID;


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gpspreet
Starting Member

8 Posts

Posted - 03/31/2013 :  01:38:09  Show Profile  Reply with Quote
Its not working... All Name columns gives Null Values.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/31/2013 :  02:56:43  Show Profile  Reply with Quote
quote:
Originally posted by gpspreet

Its not working... All Name columns gives Null Values.


That means you dont have any matching records coming in person table. Check is BusinessEntityID values in person table are correct.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gpspreet
Starting Member

8 Posts

Posted - 03/31/2013 :  23:57:47  Show Profile  Reply with Quote
Ohhh.... Yes, It worked now..

SELECT SOH.CustomerID,per.FirstName + ' ' + Per.LastName AS PersonName, TotalDue= sum(SOH.TotalDue)
FROM Sales.SalesOrderHeader SOH
Inner JOIN Sales.Customer CUS ON SOH.CustomerID = CUS.CustomerID
Left Outer JOIN
Person.BusinessEntityContact BEC ON CUS.PersonID =BEC.PersonID
Left outer JOIN
(select p.FirstName,p.MiddleName, p.LastName, BusinessEntityID from person.person p ) per ON per.BusinessEntityID = BEC.PersonID
where year(SOH.OrderDate) = '2007' and TotalDue > 100000 group by SOH.CustomerID,per.LastName,per.firstName ORDER BY SOH.CustomerID;



Change is per.BusinessEntityID = BEC.PersonID instead of BEC.BusinessEntityID..

Thanks, for Helping me through out.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  00:26:15  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.69 seconds. Powered By: Snitz Forums 2000