Item (upc, title, type, category, company, year, sellPrice)
Purchase (receiptId, date, cid, name, card#, expire, expectedDate, deliveredDate)
PurchaseItem (receiptId, upc, quantity)
Stored (name, upc, stock)
To show top selling item: The user provides a date and a number, say n. The system prints a list of the n best selling items on that day, by all the stores. For each best seller, the system shows the title, the company, the current stock and the number of copies sold. The output should be ordered according to sales: the best selling item should be first, the second best will follow, etc.
Here is my SQL code: -
select title, company, sum(stock) as current stock, sum(quantity) as quantity sold
-
from item, purchase, purchaseitem, stored
-
where purchaseitem.upc = item.upc AND purchaseitem.receiptID = purchase.receiptID AND
-
purchaseitem.upc = stored.upc AND date = ?
-
group by title
-
order by quantity sold;
-
Can anyone please tell me whether the following is right logically? and also whether it is in right Oracle syntax?
and how do you show the first selling, then the second best selling item ...
Thank you very much for your help.
23 2421
Hi,
Welcome to TSDN
Please make sure you follow POSTING GUIDELINES when ever you post in the forum
Thanks
MODERATOR
Item (upc, title, type, category, company, year, sellPrice)
Purchase (receiptId, date, cid, name, card#, expire, expectedDate, deliveredDate)
PurchaseItem (receiptId, upc, quantity)
Stored (name, upc, stock)
To show top selling item: The user provides a date and a number, say n. The system prints a list of the n best selling items on that day, by all the stores. For each best seller, the system shows the title, the company, the current stock and the number of copies sold. The output should be ordered according to sales: the best selling item should be first, the second best will follow, etc.
Here is my SQL code: -
select title, company, sum(stock) as current stock, sum(quantity) as quantity sold
-
from item, purchase, purchaseitem, stored
-
where purchaseitem.upc = item.upc AND purchaseitem.receiptID = purchase.receiptID AND
-
purchaseitem.upc = stored.upc AND date = ?
-
group by title
-
order by quantity sold;
-
Can anyone please tell me whether the following is right logically? and also whether it is in right Oracle syntax?
and how do you show the first selling, then the second best selling item ...
Thank you very much for your help.
Firstly,
your query will error out because the alias name used cannot have space in between.
"Current stock" and "quantity sold" cannot have space. if you want a space as you have now you need to enclose them with double quotes but it is not a good programming practice.
Item (upc, title, type, category, company, year, sellPrice)
Purchase (receiptId, date, cid, name, card#, expire, expectedDate, deliveredDate)
PurchaseItem (receiptId, upc, quantity)
Stored (name, upc, stock)
To show top selling item: The user provides a date and a number, say n. The system prints a list of the n best selling items on that day, by all the stores. For each best seller, the system shows the title, the company, the current stock and the number of copies sold. The output should be ordered according to sales: the best selling item should be first, the second best will follow, etc.
Here is my SQL code: -
select title, company, sum(stock) as current stock, sum(quantity) as quantity sold
-
from item, purchase, purchaseitem, stored
-
where purchaseitem.upc = item.upc AND purchaseitem.receiptID = purchase.receiptID AND
-
purchaseitem.upc = stored.upc AND date = ?
-
group by title
-
order by quantity sold;
-
Can anyone please tell me whether the following is right logically? and also whether it is in right Oracle syntax?
and how do you show the first selling, then the second best selling item ...
Thank you very much for your help.
Could you please explain why is your databse table desing such a way?
I would suggest you to place upc,quantity in the purchase table it self rather having seperate table purchaseitem which is not required.
The receipt details will have the item code ie upc and the qty sold, so these two columns can be placed in purchase table.
Firstly,
your query will error out because the alias name used cannot have space in between.
"Current stock" and "quantity sold" cannot have space. if you want a space as you have now you need to enclose them with double quotes but it is not a good programming practice.
hi there, thank you for your response
here is a modifed sql -
select title, company, sum(stock) as currentstock, sum(quantity) as quantitysold
-
from item, purchase, purchaseitem, stored
-
where purchaseitem.upc = item.upc AND purchaseitem.receiptID = purchase.receiptID AND
-
purchaseitem.upc = stored.upc
-
group by title;
-
here is the oracle error that i get:
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Could you please explain why is your databse table desing such a way?
I would suggest you to place upc,quantity in the purchase table it self rather having seperate table purchaseitem which is not required.
The receipt details will have the item code ie upc and the qty sold, so these two columns can be placed in purchase table.
Hi amitpatel66,
I did not read your second reply.
the tables are given in this way and we have to work with those tables.
Tryh this:
New table structure:
Item (upc, title, type, category, company, year, sellPrice)
Purchase (receiptId, date, cid, name, card#, expire, expectedDate, deliveredDate, upc, quantity)
Stored (name, upc, stock) -
SELECT x.name,i.title,i.company,x.curr_stock,x.qty_sold FROM
-
(SELECT t.*, row_number() OVER(partition by name ORDER BY name) rn FROM
-
(SELECT p.name,p.date,p.upc,SUM(s.stock) OVER(PARTITION BY name,upc) curr_stock,SUM(p.quantity) OVER(partition by name,ucd) qty_sold FROM purchase p,stock s
-
WHERE p.upc = s.upc AND p.name = s.name
-
ORDER BY p.name,qty_sold DESC) t) x, item i
-
WHERE x.upc = i.upc
-
AND rn < = '&1'
-
AND TO_DATE(x.date,'DD-MON-YYYY') = TO_DATE('&2','DD-MON-YYYY')
-
Note: Query not tested!!
Hi amitpatel66,
I did not read your second reply.
the tables are given in this way and we have to work with those tables.
Try this query: -
SELECT x.name,i.title,i.company,x.curr_stock,x.qty_sold FROM
-
(SELECT t.*, row_number() OVER(partition by name ORDER BY name) rn FROM
-
(SELECT p.name,p.date,p.upc,SUM(s.stock) OVER(PARTITION BY name,upc) curr_stock,SUM(p.quantity) OVER(partition by name,ucd) qty_sold FROM purchase p,stock s,purchaseitem pi
-
WHERE pi.upc = s.upc AND pi.name = s.name
-
AND pi.receiptid= p.receiptid
-
ORDER BY p.name,qty_sold DESC) t) x, item i
-
WHERE x.upc = i.upc
-
AND rn < = '&1'
-
AND TO_DATE(x.date,'DD-MON-YYYY') = TO_DATE('&2','DD-MON-YYYY')
-
Note: Not tested!!
Try this query: -
SELECT x.name,i.title,i.company,x.curr_stock,x.qty_sold FROM
-
(SELECT t.*, row_number() OVER(partition by name ORDER BY name) rn FROM
-
(SELECT p.name,p.date,p.upc,SUM(s.stock) OVER(PARTITION BY name,upc) curr_stock,SUM(p.quantity) OVER(partition by name,ucd) qty_sold FROM purchase p,stock s,purchaseitem pi
-
WHERE pi.upc = s.upc AND pi.name = s.name
-
AND pi.receiptid= p.receiptid
-
ORDER BY p.name,qty_sold DESC) t) x, item i
-
WHERE x.upc = i.upc
-
AND rn < = '&1'
-
AND TO_DATE(x.date,'DD-MON-YYYY') = TO_DATE('&2','DD-MON-YYYY')
-
Note: Not tested!!
As I have already said, I can not change the tables. This is an assignment and your sql looks very complicated that whas it is in my textbook
can someone please tell me what the grouyby error is?
Item (upc, title, type, category, company, year, sellPrice)
Purchase (receiptId, date, cid, name, card#, expire, expectedDate, deliveredDate)
PurchaseItem (receiptId, upc, quantity)
Stored (name, upc, stock)
I have the following tables can anyone please tell me what is wrong with my sql statement: -
select title, company, sum(stock) as currentstock, sum(quantity) as quantitysold
-
from item, purchase, purchaseitem, stored
-
where purchaseitem.upc = item.upc AND purchaseitem.receiptID = purchase.receiptID AND
-
purchaseitem.upc = stored.upc
-
group by title;
-
The user provides a date and a number, say n. The system prints a list of the n best selling items on that day, by all the stores. For each best seller, the system shows the title, the company, the current stock and the number of copies sold. The output should be ordered according to sales: the best selling item should be first, the second best will follow, etc.
Thanks.
As I have already said, I can not change the tables. This is an assignment and your sql looks very complicated that whas it is in my textbook
can someone please tell me what the grouyby error is?
Since u said that u dont want change in table structure, i have made change in my query to work as per your table structure.
Did you put an effort to test that query in your instance??
I cant test for you because i dont have table structure and data in my instance.
As I have already said, I can not change the tables. This is an assignment and your sql looks very complicated that whas it is in my textbook
can someone please tell me what the grouyby error is?
You will get GROUP BY ERROR if you dont inclue all the columns that you have in SELECT with GROUP BY clause. you have just included title in GROUP by clause, you also need to include other columns with GROUP BY to make your query atleast exectue without any error.
Threads MERGED for better management of the FORUM
MODERATOR
Check out this one:
The query is tested and it works fine for your requirement: -
-
SQL> SELECT i.title "Product Name",y.name "Store Name",y.qty_sold "Qty Sold",y.current_stock "Current Stock",y.upc "Product ID" FROM
-
2 (SELECT x.*,ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rn FROM
-
3 (SELECT p.name,sum(pu.qty) QTY_SOLD,SUM(DISTINCT s.stock) CURRENT_STOCK,pu.upc FROM purchase p,pi pu,store s WHERE
-
4 p.repid = pu.repid
-
5 AND s.upc = pu.upc
-
6 AND s.name = p.name
-
7 AND TRUNC(p.dat) = TRUNC(&Enter_Date)
-
8 GROUP BY p.name,pu.upc
-
9 ORDER BY p.name,qty_sold DESC,pu.upc) x) y,item i
-
10 WHERE i.upc = y.upc
-
11 AND y.rn <= '&Enter_N_best_sell'
-
12 ORDER BY y.name
-
13 /
-
Enter value for enter_date: SYSDATE -- Date for which info is reqd
-
old 7: AND TRUNC(p.dat) = TRUNC(&Enter_Date)
-
new 7: AND TRUNC(p.dat) = TRUNC(SYSDATE)
-
Enter value for enter_n_best_sell: 3 -- Top 3 best sales for each stores
-
old 11: AND y.rn <= '&Enter_N_best_sell'
-
new 11: AND y.rn <= '3'
-
-
Product Name Store Name Qty Sold Current Stock Product ID
-
-------------------- -------------------- --------- ------------- --------------------
-
PEN ABC 2 10 1
-
PENCIL ABC 6 10 2
-
RUBBER ABC 10 10 3
-
PEN BCD 6 10 1
-
PENCIL BCD 1 10 2
-
-
SQL>
-
-
dear amitpatel,
Thank for your help but your code is a bit complicated for a newbie like me.
I will show you what I have:
The first block of sql statements gives the right result,
the second sql block also give the right result, but when i join them together i get wrong result and I know what it is happening. Can you please tell me how I can join the two sql blocks so that i get
title company sumofquanitity sumofstock
Please use my own variables. -
-
1) select title, company, sum(quantity) as quantitysold
-
from item, purchaseitem
-
where purchaseitem.upc = item.upc
-
group by title, company; {it works}
-
2) 2) select title, company, sum(stock) as currentstock
-
from item, stored
-
where item.upc = stored.upc
-
group by title, company) {it also works}
-
3) DOES NOT WORK: 3) select title, company, sum(quantity) as quantitysold, sum(stock) as currentstock
-
from item, purchaseitem, stored
-
where purchaseitem.upc = item.upc and item.upc = stored.upc
-
group by title, company; { it does not work}
-
-
Please enclose your posted code in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use [code] tags in future.
MODERATOR
Please enclose your posted code in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use [code] tags in future.
MODERATOR
sorry, I will do that in future. Will I get a response please?
dear amitpatel,
Thank for your help but your code is a bit complicated for a newbie like me.
I will show you what I have:
The first block of sql statements gives the right result,
the second sql block also give the right result, but when i join them together i get wrong result and I know what it is happening. Can you please tell me how I can join the two sql blocks so that i get
title company sumofquanitity sumofstock
Please use my own variables. -
-
1) select title, company, sum(quantity) as quantitysold
-
from item, purchaseitem
-
where purchaseitem.upc = item.upc
-
group by title, company; {it works}
-
2) 2) select title, company, sum(stock) as currentstock
-
from item, stored
-
where item.upc = stored.upc
-
group by title, company) {it also works}
-
3) DOES NOT WORK: 3) select title, company, sum(quantity) as quantitysold, sum(stock) as currentstock
-
from item, purchaseitem, stored
-
where purchaseitem.upc = item.upc and item.upc = stored.upc
-
group by title, company; { it does not work}
-
-
If you dont want to find the top N best sell for each store then try this query: -
SELECT i.title,i.company,x.name,x.qty_sold,x.current_stock FROM
-
(SELECT p.name,sum(pu.qty) QTY_SOLD,SUM(DISTINCT s.stock) CURRENT_STOCK, pu.upc
-
FROM purchase p,pi pu,store s
-
WHERE
-
p.repid = pu.repid
-
AND s.upc =pu.upc
-
AND s.name = p.name
-
AND TRUNC(p.dat) = TRUNC(&Enter_Date)
-
GROUP BY p.name,pu.upc
-
ORDER BY p.name,qty_sold DESC,pu.upc) x , item i
-
WHERE i.upc = x.upc
-
ORDER by x.name
-
/
-
This is simple query which will give you the sell of all the items of all the store on a particular day.
&1 - input parameter (Date)
hi amitpatel: -
SQL> select * from stored;
-
STORENAME UPC STOCK
-------------------- ---------- ----------
broadway 1 10
broadway 2 10
broadway 3 10
broadway 4 10
ubc 1 10
ubc 2 10
ubc 3 10
ubc 4 10
broadway 11 10
ubc 11 10 -
select i.title, i.company, temp.qty_sold, temp.current_stock from
-
(select p.storename, sum(pu.quantity) qty_sold, sum(s.stock) current_stock, pu.upc
-
from purchase p, purchaseitem pu, stored s
-
where p.receiptid = pu.receiptid AND s.upc = pu.upc AND s.storename = p.storename
-
group by p.storename, pu.upc
-
order by p.storename, qty_sold DESC, pu.upc) temp, item i
-
where i.upc = temp.upc
-
order by temp.storename;
-
Running your sql above, give the right result for quantity sold but not for quantity in stock
each item should have 20 in stock
i think we can not have two aggregate operators in one select statement?
any ideas?
Thanks.
hi amitpatel:
SQL> select * from stored;
STORENAME UPC STOCK
-------------------- ---------- ----------
broadway 1 10
broadway 2 10
broadway 3 10
broadway 4 10
ubc 1 10
ubc 2 10
ubc 3 10
ubc 4 10
broadway 11 10
ubc 11 10 -
select i.title, i.company, temp.qty_sold, temp.current_stock from
-
(select p.storename, sum(pu.quantity) qty_sold, sum(s.stock) current_stock, pu.upc
-
from purchase p, purchaseitem pu, stored s
-
where p.receiptid = pu.receiptid AND s.upc = pu.upc AND s.storename = p.storename
-
group by p.storename, pu.upc
-
order by p.storename, qty_sold DESC, pu.upc) temp, item i
-
where i.upc = temp.upc
-
order by temp.storename;
-
Running your sql above, give the right result for quantity sold but not for quantity in stock
each item should have 20 in stock
i think we can not have two aggregate operators in one select statement?
any ideas?
Thanks.
If I go back and check your requirement, you said you want the Stock for all the items STORE WISE, SO it will not sum up the STOCK for the same item of different stores.
Please tell me your exact requirement??
If you need current stock,item title,qty sold for all the items regardless the store from where the item is sold/stocked, then this is the query. the simple one: -
SQL> SELECT title,company,cs "Current Stock",qs "Qty Sold" FROM
-
2 (SELECT upc,(SELECT SUM(stock) from store WHERE upc = p.upc) cs ,SUM(qty) qs FROM pi p
-
3 GROUP BY p.upc) x,item i
-
4 WHERE i.upc = x.upc
-
5 /
-
-
TITLE COMPANY Current Stock Qty Sold
-
-------------------- -------------------- ------------- ---------
-
PEN COMPANY1 20 8
-
PENCIL COMPANY1 36 7
-
RUBBER COMPANY1 20 10
-
dear amipatel,
I don't know how much I should thank you. I also have added a the date field. -
select title, company, cs "currentstock", qs "quantitysold" from
-
(select upc, (select sum(stock) from stored where upc = pi.upc) cs, sum(quantity) qs from purchaseitem pi, purchase p where pi.receiptid = p.receiptid AND purchasedate = '15-NOV-2007'
-
group by pi.upc) temp, item i
-
where i.upc = temp.upc
-
order by qs DESC;
-
The only think I would like to select let's say the 2 best selling items. ROWNUM<=2
I tried many different ways to put in a where clause ROWNUM<=2 but I get different result.
Thanks alot amitpatel,
Hi amitpatel,
I got it. I figured it out how to select the top two for ie,
I like to thank you so much for all your help. I am becoming more expert in sql.
Hi amitpatel,
I got it. I figured it out how to select the top two for ie,
I like to thank you so much for all your help. I am becoming more expert in sql.
We are happy that its working for you now
You are welcome :)
Do POST for any issues in future!!
Amit
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Memduh Durmaz |
last post by:
Hi,
I'm using DB2 UDB 7.2.
Also I'm doing some tests on SQL Server 2000 for some statements to
use efectively.
I didn't find any solution on Sql Server about WITH ... SELECT
structure of DB2.
...
|
by: Cat |
last post by:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I'm getting a validation error when I try to restrict the content of nested
groups with xs:redefine whereas the same restriction on xs:element's...
|
by: ---- |
last post by:
Here are my files (problem follows the code):
--------------------------------------------------------------------------
// Group.h
#ifndef GROUP_H
#define GROUP_H
class Group {
public:
|
by: Carlos Andr?s |
last post by:
Hi everybody.
I've got a problem. I'd like to avoid opening a new window when you
have pressed the shift key and you click in the left button of the
mouse. I've tried the next solution, in the...
|
by: michael |
last post by:
Is it possible to get all href URLs contained in a unordered list and place
them in an array?
Or in fact two different arrays, differently named one for each <ul> group?
<ul>
<li><a...
|
by: Melissa |
last post by:
I have a grouped report that starts a new page at each group. All the field
labels for the report are in the group header because there is a subreport
in the top part of the report that needs to...
|
by: ChadDiesel |
last post by:
Hello everyone,
I'm having a problem with Access that I need some help with. The short
version is, I want to print a list of parts and part quantities that belong
to a certain part group---One...
|
by: Mark |
last post by:
I must create a routine that finds tokens in small, arbitrary VB code
snippets. For example, it might have to find all occurrences of
{Formula}
I was thinking that using regular expressions...
|
by: sunbeam |
last post by:
Short Description of the Project:
we developed a e-learning system for our students. each student has a unique username/password to view the modules he/she should view and nothing more. since we...
|
by: Bryan |
last post by:
A Person can belong to one group.
I want to be able to set the group using these two methods:
PersonA.Group = GroupA
or
GroupA.Add(PersonA)
In the Set method of Person.Group, the Person...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |