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
 Database Design and Application Architecture
 Help with query

Author  Topic 

rickman67
Starting Member

6 Posts

Posted - 2013-06-05 : 04:11:21
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

52326 Posts

Posted - 2013-06-05 : 04:19:17
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

6 Posts

Posted - 2013-06-05 : 04:26:45
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 04:40:05
>> 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

52326 Posts

Posted - 2013-06-05 : 04:44:18
[code]
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
[/code]
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

6 Posts

Posted - 2013-06-05 : 04:45:51
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

52326 Posts

Posted - 2013-06-05 : 04:49:18
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 04:50:11
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 04:50:57
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

6 Posts

Posted - 2013-06-05 : 04:55:28
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

52326 Posts

Posted - 2013-06-05 : 05:02:02
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

6 Posts

Posted - 2013-06-05 : 05:03:56
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

52326 Posts

Posted - 2013-06-05 : 05:09:23
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
Go to Top of Page

rickman67
Starting Member

6 Posts

Posted - 2013-06-05 : 05:12:33
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

52326 Posts

Posted - 2013-06-05 : 05:18:06
welcome

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

- Advertisement -