newbie help pls

Question

Post reply

newbie help pls

WonderPra

SSCommitted

Points: 1624
More actions
October 14, 2012 at 2:12 pm

#283025

Customer
CustomerIDCustomerNameBirthdate
1John Doe1/1/1970 08:31 AM
2Jane Doe1/1/1971 01:18 PM
3Jon Public1/1/1972 11:58 PM
4Jane Public1/1/1973 07:00 AM
5John Smith1/1/1974 08:31 AM
Order
OrderIDPO NumberOrderDate
1000ABC1231/1/2012 01:00 PM
20001122332/1/2012 02:00 AM
3000XYZ9873/1/2012 03:00 PM
4000500004/1/2012 04:00 AM
5000Verbal5/1/2012 05:00 AM
CustomerOrders
CustomerIDOrderIDIsShipped
11000 False
13000 True
34000 False
22000 True
55000 True
:-)pls provide the SQL statement that will return all the CustomerNames who have never placed an order
--Pra:-):-)--------------------------------------------------------------------------------

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

jfdelette SSC-Addicted Points: 471 More actions · Answer 1

Hi,

SELECT CustomerName FROM Customer c

LEFT OUTER JOIN CustomersOrders co ON c.CustomerID = co.CustomerID

WHERE co.CustomerID is null

Jeff.

WonderPra SSCommitted Points: 1624 More actions · Answer 2

Thanku Jeff

--Pra:-):-)--------------------------------------------------------------------------------

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Here's how you should lay out your sample data: -

CREATE TABLE Customer (CustomerID INT, CustomerName VARCHAR(200), Birthdate DATETIME);

INSERT INTO Customer

SELECT CustomerID,CustomerName,Birthdate

FROM (VALUES(1,'John Doe','1/1/1970 08:31 AM'),(2,'Jane Doe','1/1/1971 01:18 PM'),

(3,'Jon Public','1/1/1972 11:58 PM'),(4,'Jane Public','1/1/1973 07:00 AM'),

(5,'John Smith','1/1/1974 08:31 AM'))a(CustomerID,CustomerName,Birthdate);

CREATE TABLE [Order] (OrderID INT, [PO Number] VARCHAR(10), OrderDate DATETIME);

INSERT INTO [Order]

SELECT OrderID, [PO Number], OrderDate

FROM (VALUES(1000,'ABC123','1/1/2012 01:00 PM'),(2000,'112233','2/1/2012 02:00 AM'),

(3000,'XYZ987','3/1/2012 03:00 PM'),(4000,'50000','4/1/2012 04:00 AM'),

(5000,'Verbal','5/1/2012 05:00 AM'))a(OrderID, [PO Number], OrderDate);

CREATE TABLE CustomerOrders (CustomerID INT, OrderID INT, IsShipped VARCHAR(5));

INSERT INTO CustomerOrders

SELECT CustomerID, OrderID, IsShipped

FROM (VALUES(1,1000,'False'),(1,3000,'True'),(3,4000,'False'),(2,2000,'True'),

(5,5000,'True'))a(CustomerID, OrderID, IsShipped);

That makes it so that anyone can just execute the script and have a mock-up of your data. You should include any indexes as well if you want the best performing solution.

Jeff's method is fine, but to show another method you could also do this: -

SELECT *

FROM Customer cus

WHERE NOT EXISTS (SELECT 1

FROM CustomerOrders ord

WHERE cus.CustomerID = ord.CustomerID);

They probably perform the same, but it's worth testing both methods on your actual data.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WonderPra SSCommitted Points: 1624 More actions · Answer 4

For the same data Provide the SQL statement that would return every CustomerName and the count of orders they have ever placed.

I have tried using the below code

select Customer.CustomerName, Count(CustomerOrders.customerID) from Customer JOIN CustomerOrders ON customer.customerID=customerorders.CustomerID

giving an error:

Msg 8120, Level 16, State 1, Line 1

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

--Pra:-):-)--------------------------------------------------------------------------------

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

prathibha_aviator (10/15/2012)
For the same data Provide the SQL statement that would return every CustomerName and the count of orders they have ever placed.
I have tried using the below code
select Customer.CustomerName, Count(CustomerOrders.customerID) from Customer JOIN CustomerOrders ON customer.customerID=customerorders.CustomerID
giving an error:
Msg 8120, Level 16, State 1, Line 1
Column 'Customer.CustomerName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The error message tells you EXACTLY what the problem is. You need to use some grouping when aggregating data.

http://msdn.microsoft.com/en-us/library/ms177673%28v=sql.105%29.aspx

--edit--

I did not provide an answer because this scream of homework. If I provide you an answer you do not learn the topic.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

prathibha_aviator (10/15/2012)
For the same data Provide the SQL statement that would return every CustomerName and the count of orders they have ever placed.
I have tried using the below code
select Customer.CustomerName, Count(CustomerOrders.customerID) from Customer JOIN CustomerOrders ON customer.customerID=customerorders.CustomerID
giving an error:
Msg 8120, Level 16, State 1, Line 1
Column 'Customer.CustomerName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

~~Read your error message, then read this.~~

~~GROUP BY Customer.CustomerName~~

~~Make sure you read the link I've provided to explain the issue.~~

--EDIT--

Seems Sean beat me to it.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WonderPra SSCommitted Points: 1624 More actions · Answer 7

So I get to use joins concept along with the group by clause in this case as i am going to use two differnt tables??? Not a question to you... I'll give it a try and get back again

--Pra:-):-)--------------------------------------------------------------------------------

WonderPra SSCommitted Points: 1624 More actions · Answer 8

I got it

SELECT Customer.CustomerName, Count(customerorders.customerID) AS Count

FROM Customer

JOIN CustomerOrders ON (Customer.CustomerID= CustomerOrders.CustomerID)

Group By CustomerName

--Pra:-):-)--------------------------------------------------------------------------------

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

prathibha_aviator (10/15/2012)
I got it
SELECT Customer.CustomerName, Count(customerorders.customerID) AS Count
FROM Customer
JOIN CustomerOrders ON (Customer.CustomerID= CustomerOrders.CustomerID)
Group By CustomerName

Perfect.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

WonderPra SSCommitted Points: 1624 More actions · Answer 10

Hey Thanku 🙂

--Pra:-):-)--------------------------------------------------------------------------------