SQL PRoblem with GROUP BY

[lionelm]

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.

[amitpatel66]

Hi,

Welcome to TSDN

Please make sure you follow POSTING GUIDELINES when ever you post in the forum

Thanks
MODERATOR

[amitpatel66]

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.

[amitpatel66]

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.

[lionelm]

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

[lionelm]

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.

[amitpatel66]

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!!

[amitpatel66]

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!!

[lionelm]

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?

[lionelm]

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.

[amitpatel66]

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.

[amitpatel66]

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.

[amitpatel66]

Threads MERGED for better management of the FORUM

MODERATOR

[amitpatel66]

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> 
 
 

[lionelm]

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}
 
 

[amitpatel66]

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

[lionelm]

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?

[amitpatel66]

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)

[lionelm]

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.

[amitpatel66]

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??

[amitpatel66]

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
 

[lionelm]

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,

[lionelm]

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.

[amitpatel66]

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