AdventureWorks2012 query: get name and ID of cust

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

SQL Server 2012 Forums

Transact-SQL (2012)

AdventureWorks2012 query: get name and ID of cust

Author

Topic

gpspreet
Starting Member

8 Posts

Posted - 2013-03-30 : 00:40:04

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

52326 Posts

Posted - 2013-03-30 : 01:52:49

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/

gpspreet
Starting Member

8 Posts

Posted - 2013-03-30 : 03:42:03

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-30 : 03:45:27

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/

gpspreet
Starting Member

8 Posts

Posted - 2013-03-30 : 03:56:10

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-30 : 04:01:47

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/

gpspreet
Starting Member

8 Posts

Posted - 2013-03-30 : 05:04:34

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-30 : 13:05:48

[code]
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;
[/code]

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

gpspreet
Starting Member

8 Posts

Posted - 2013-03-31 : 01:38:09

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-31 : 02:56:43

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/

gpspreet
Starting Member

8 Posts

Posted - 2013-03-31 : 23:57:47

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 00:26:15

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources