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
 General SQL Server Forums
 Database Design and Application Architecture
 Help with query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rickman67
Starting Member

United Kingdom
6 Posts

Posted - 06/05/2013 :  04:11:21  Show Profile  Reply with Quote
Hi Guys

I am tasked with retrieving certain info from an SQL created database.

There is one particular query I am struggling with.

First here is how the tables were created

Create table customers
(cust_id char(6) not null,
name char(30),
phone char(15),
constraint pk_customers1 Primary Key (cust_id));

CREATE TABLE products1
(prod_id varchar2(8) NOT NULL,
prod_name char(30),
price number(10,2),
on_hand varchar2(10),
supp_id varchar2(8),
constraint pk_products2 Primary Key (prod_id),
constraint fk_products2 Foreign Key (supp_id) references suppliers5(supp_id));

create table sales2
(cust_id varchar2(6) NOT NULL,
prod_id varchar2(8) NOT NULL,
quantity smallint,
date_of_sale varchar(9),
constraint pk_sales PRIMARY KEY (cust_id, prod_id));

CREATE TABLE suppliers5
(supp_id varchar2(6) NOT NULL,
company_name varchar2(15),
town varchar2(15),
phone varchar2(15),
constraint pk_suppliers PRIMARY KEY (supp_id));


The query I am struggling with taks me with the following

some of your products are not selling, you would like a list of sales which also includes those products which have not sold any during the period covered by the tables. Display product ID, the product name, and the date of sale for all products sold. Also include those products which have NOT sold.

The code I am using is

select p.prod_id as product, p.prod_name as "product name", s.date_of_sale
from products1 p, sales67 s
WHERE s.date_of_sale >= '01-Jan-06'
AND p.prod_id = s.prod_id

I now need to add a command to this that will show the products that have not sold.

I have been at this for days. I am prerparing for an exam.

Please can someone help?

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  04:19:17  Show Profile  Reply with Quote
you need to use left join for that


select p.prod_id as product, p.prod_name as "product name", s.date_of_sale
from products1 p
left join sales67 s
on p.prod_id = s.prod_id
AND s.date_of_sale >= '01-Jan-06'


Also few other things

1. use ANSI style joins rather than old join syntax as it adds more clarity
2. didnt understand why you've object names like sales2,supplier5 etc. why those numbers in the end?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rickman67
Starting Member

United Kingdom
6 Posts

Posted - 06/05/2013 :  04:26:45  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

you need to use left join for that


select p.prod_id as product, p.prod_name as "product name", s.date_of_sale
from products1 p
left join sales67 s
on p.prod_id = s.prod_id
AND s.date_of_sale >= '01-Jan-06'


Also few other things

1. use ANSI style joins rather than old join syntax as it adds more clarity
2. didnt understand why you've object names like sales2,supplier5 etc. why those numbers in the end?

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




Thank you my friend you have saved my life.

I will take your advice under consideration I have been taught it the old way. Need to catch up.

The tables have number after them because there are already tables with the same names. Long story.

While you are on I wonder if you could help me one more time.

Hope its not cheeky to ask.

I need to show the most popular selling item in terms of quality sold. I must display the product ID, product name, and quantity sold.

I gave up with this one, if you could help you would make a man very happy.

Thanks in advance kind sir
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 06/05/2013 :  04:40:05  Show Profile  Reply with Quote
>> most popular selling item in terms of quantity sold. I must display the product ID, product name, and quantity sold.
SELECT p.Prod_id, prod_name, SUM(s.quantity) TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  04:44:18  Show Profile  Reply with Quote

SELECT TOP 1 WITH TIES p.Prod_id, prod_name, SUM(s.quantity) TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC

Will give you most popular Product based on quantity sold

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rickman67
Starting Member

United Kingdom
6 Posts

Posted - 06/05/2013 :  04:45:51  Show Profile  Reply with Quote
quote:
Originally posted by bandi

>> most popular selling item in terms of quantity sold. I must display the product ID, product name, and quantity sold.
SELECT p.Prod_id, prod_name, SUM(s.quantity) TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC

--
Chandu



Hi Chandu

thanks for getting back to me

when I do this it lists all the products and their quantities sold I just want to show the one product that has sold the most can you help
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  04:49:18  Show Profile  Reply with Quote
quote:
Originally posted by rickman67


Hi Chandu

thanks for getting back to me

when I do this it lists all the products and their quantities sold I just want to show the one product that has sold the most can you help


See my last posted suggestion
It should give you what you're after

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 06/05/2013 :  04:50:11  Show Profile  Reply with Quote
quote:
Originally posted by rickman67

quote:
Originally posted by bandi

>> most popular selling item in terms of quantity sold. I must display the product ID, product name, and quantity sold.
SELECT TOP 1 WITH TIES p.Prod_id, prod_name, SUM(s.quantity) TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC

--
Chandu



Hi Chandu

thanks for getting back to me

when I do this it lists all the products and their quantities sold I just want to show the one product that has sold the most can you help



--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 06/05/2013 :  04:50:57  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by rickman67


Hi Chandu

thanks for getting back to me

when I do this it lists all the products and their quantities sold I just want to show the one product that has sold the most can you help


See my last posted suggestion
It should give you what you're after

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



Oh I haven't checked your post visakh...

--
Chandu
Go to Top of Page

rickman67
Starting Member

United Kingdom
6 Posts

Posted - 06/05/2013 :  04:55:28  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by rickman67


Hi Chandu

thanks for getting back to me

when I do this it lists all the products and their quantities sold I just want to show the one product that has sold the most can you help


See my last posted suggestion
It should give you what you're after

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




Hi when I input this it tells me that from keyword not found where expected
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  05:02:02  Show Profile  Reply with Quote
you mean when you used this suggestion?


SELECT TOP 1 WITH TIES p.Prod_id, prod_name, SUM(s.quantity) AS TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC


Are you using sql server? whats the version used?
run the below query and post the result

SELECT @@VERSION

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rickman67
Starting Member

United Kingdom
6 Posts

Posted - 06/05/2013 :  05:03:56  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

you mean when you used this suggestion?


SELECT TOP 1 WITH TIES p.Prod_id, prod_name, SUM(s.quantity) AS TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC


Are you using sql server? whats the version used?
run the below query and post the result

SELECT @@VERSION

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




hi yeah I am using that one

that did not work when I inputted the command to get the version

It is my sql through oracle application express I am using
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  05:09:23  Show Profile  Reply with Quote
quote:
Originally posted by rickman67

quote:
Originally posted by visakh16

you mean when you used this suggestion?


SELECT TOP 1 WITH TIES p.Prod_id, prod_name, SUM(s.quantity) AS TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC


Are you using sql server? whats the version used?
run the below query and post the result

SELECT @@VERSION

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




hi yeah I am using that one

that did not work when I inputted the command to get the version

It is my sql through oracle application express I am using


Oracle 9i and above use

SELECT Prod_id, prod_name,TotalSalesperProduct
FROM
(
SELECT DENSE_RANK() OVER (ORDER BY  SUM(s.quantity) DESC) AS Rnk,p.Prod_id, prod_name, SUM(s.quantity) AS TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
)t
WHERE rnk=1


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

Edited by - visakh16 on 06/05/2013 05:11:20
Go to Top of Page

rickman67
Starting Member

United Kingdom
6 Posts

Posted - 06/05/2013 :  05:12:33  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by rickman67

quote:
Originally posted by visakh16

you mean when you used this suggestion?


SELECT TOP 1 WITH TIES p.Prod_id, prod_name, SUM(s.quantity) AS TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC


Are you using sql server? whats the version used?
run the below query and post the result

SELECT @@VERSION

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




hi yeah I am using that one

that did not work when I inputted the command to get the version

It is my sql through oracle application express I am using


Oracle 9i and above use

SELECT Prod_id, prod_name,TotalSalesperProduct
FROM
(
SELECT DENSE_RANK() OVER (ORDER BY  SUM(s.quantity) DESC) AS Rnk,p.Prod_id, prod_name, SUM(s.quantity) AS TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
)t
WHERE rnk=1


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




Legend all sorted now thank you

I will be using forum again
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  05:18:06  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.12 seconds. Powered By: Snitz Forums 2000