Hi Guys,
I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...
Is their a way to force 1 column to always be unique?
Heres the query:
SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC
The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.
Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
Which is kinda annoying... is there anyway i can prevent this?
And also apart from the Where Joins... is there a more efficient way of writing this?
thx for reading :-)
--PhilkillsDoes no one know any way around this?|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.
on the contrary, i left out the data on purpose as it was irrelevent... i was only trying to point out an example of whats wrong... in the "EXAMPLE"
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
The parts in bold should only appear once.... and the data it should select should be Mathew smith who purchased 30 of that item... 36 is the total amount sold, what i need the query to do... is to only display the person who bought the highest amount of the item that is part of the top 5 best selling items.
another "EXAMPLE":
ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................30......... ......................Jim
2.........20.................20......... ......................Jam
3.........10.................10......... ......................Flim
4.........5..................5......... .......................Flam
5.........2..................2......... .......................Stam
In this case... only 1 person purchased the items in the top 5...
However, if 5 people purchased the same item... the query would return:
ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................5.......... .....................Jim
1.........30.................5.......... .....................Jam
1.........30.................5.......... .....................Flim
1.........30.................5.......... .....................Flam
1.........30.................10......... .....................Stam
Notice how it returned the TOP 1 Item instead of the TOP 5 items?
The data is correct... the query is wrong... which is why i asked if i could put a constraint on the data returned saying that ItemID MUST be unique... unless ofcourse there is a better way to do it ;p
I hope this more accurately describes the problem i am having. :-)
--Philkills|||You are making this more confusing than it should be.
Lets break it down into parts.
Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||You are making this more confusing than it should be.
Lets break it down into parts.
Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
yep that code gives the top 5 items ^^|||...and now you want the top customer for each of those items?|||...and now you want the top customer for each of those items?
correct...|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
Theres no table called CUSTOMER_ITEM... However, i have edited it to return the top customerID correctly.
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ITEM
INNER JOIN ORDER_ITEMOrdItem2 ON ITEM.ItemID = OrdItem2.ItemID
INNER JOIN ORDER_T ON OrdItem2.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC,
ORDER.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM_0510096.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||ok, so i can display the customer ID... now how do i display the rest of the customer info ... ^_^|||Am i assuming that no one knows the answer? :confused:
lol, kinda funny a problem that even professionals can't figure out :p|||lol, kinda funny a problem that even professionals can't figure out :poh stop
yes, there are a lot of professionals here
what makes you think they want to be your personal free development center?
ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person
good luck, phil
:)|||oh stop
yes, there are a lot of professionals here
what makes you think they want to be your personal free development center?
ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person
good luck, phil
:)
Eh?, i asked 1 question in this thread... and simply gave more info... in my other posts...
If your refering to my other questions that i've asked around the forum... well, they are pretty much unrelated to this question. I have done a lot of research on SQL queries and the only questions i've asked are related to areas that i have found very difficult to find an answer to.
So as a last resort i turned to this support forum, now i realise your doing this out of the kindness of your heart (i think) and for that im grateful. I too help people, but my area of expertise is with C++/Other languages, i on the other hand will help people regardless of the complexity of the problem, infact the more complex the better :p keeps things interesting.
In regards to my original point about no one being able to solve the problem... well, that was based on the posts inside this thread:
OK. I lied. This is complicated, but we are almost there...
He admitted himself that this was complicated, still being new to SQL i do not yet know all of its limits... but when a proffessional admits that something is complicated, i would say its a pretty good indication that it actually is complicated...
When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.
So perhaps next time, before jumping in and making assumptions... you should consider all the facts and why i might have said such words.|||When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.
I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.|||I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.
I.... was not intending to be rude
YOU.... Misinterpreted my point.. obviously thinking i was taking a dig at you or your skill when i was not...
I.... Appreciate all your help so far
YOU.... Probably won't even read this :p
It's up to you whether you help me or not i suppose... but just for the record... the only reason i may have seemed slightly impatient was due to the ever approaching deadline :confused:|||as the old saying goes, "failure to plan on your part does not constitute an emergency on our part"
i'm sorry about your homework assignment coming due real soon, but that's life
if you would like to start a new thread, we can close this one and let it fade into the archives...|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.
And the fact that now it turns out that this was some sort of homework assignment irks me even more.|||Not to bump a thread that seems to have everyone up in arms, but...
I like to try to solve these "puzzles", just to see if I can.
I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.
Using my own data, I came up with this:
SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC
This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.
And the fact that now it turns out that this was some sort of homework assignment irks me even more.
again suprise suprise you jump to conclusions that you think are right... i was NOT implying anything... if something can't be done... it can't be done... thats why i said what i did...
Not to bump a thread that seems to have everyone up in arms, but...
I like to try to solve these "puzzles", just to see if I can.
I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.
Using my own data, I came up with this:
Code:
SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC
This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549
thank you very much mate for putting in the effort to solve this conundrum
:-). I appreciate it i really do ^^|||Am i assuming that no one knows the answer? :confused:
lol, kinda funny a problem that even professionals can't figure out :p
i on the other hand will help people regardless of the complexity of the problem,
When he never replied after that post... i just assumed that he gave up and admitted defeat...
YOU.... Probably won't even read this
You were acting like a complete twit. The fact that you seem oblivious to this I will chalk up to immaturity. Hopefully, you will soon grow up enough to be respectful to people who are volunteering their time and expertise to help you out.|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.
I intended the top 5 in the outer query to pull the top 5 customers, while
the top 5 in the nested query is pulling just the top 5 products.
sql
MrHat wrote: