Help with query - SQL Server Forums

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources