question

pinar avatar image
pinar asked

modify sql query to get distinctive results

Hi, I am struggling to change the SELECT statement so that it only displays the staff once. What it is meant to do is to display staffs' MAX and MIN of preparation time of particular type of order. There are 3 types of orders. The following statement so far displays all orders made by staff and MIN and MAX of each order, I would like it to display MIN and MAX for each type of order, therefore, statement should only return three rows. SELECT DISTINCT User.USERID, User.FIRST_NAME, User.SURNAME, User.EMAIL, STAFF.STAFFID, Order.ORDERID, Order.ORDER_DATE, Order.ORDER_TYPE, MAX(DISTINCT Order.ORDER_PREP_TIME) AS MAX_FINISH, MIN(DISTINCT Order.ORDER_PREP_TIME) AS MIN_FINISH From Order INNER JOIN STAFF ON Order.STAFFID = STAFF.STAFFID INNER JOIN User ON STAFF.USERID = User.USERID WHERE MONTH(Order.ORDER_DATE) = SOME_MONTH AND YEAR(Order.ORDER_DATE) = SOME_YEAR GROUP BY User.FIRST_NAME, User.SURNAME, User.EMAIL, User.USERID, Order.ORDERID, Order.ORDER_DATE, Order.TIME_ORDERED, STAFF.STAFFID, Order.ORDER_TYPE (If change GROUP BY to GROUP BY Order.ORDER_TYPE I get an error and I need to include all the tables.)
sqlapache-derby
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sule avatar image
Sule answered
First option: SELECT DISTINCT User.USERID, User.FIRST_NAME, User.SURNAME, User.EMAIL, STAFF.STAFFID, Order.ORDER_TYPE, MAX(DISTINCT Order.ORDER_PREP_TIME) AS MAX_FINISH, MIN(DISTINCT Order.ORDER_PREP_TIME) AS MIN_FINISH From Order INNER JOIN STAFF ON Order.STAFFID = STAFF.STAFFID INNER JOIN User ON STAFF.USERID = User.USERID WHERE MONTH(Order.ORDER_DATE) = SOME_MONTH AND YEAR(Order.ORDER_DATE) = SOME_YEAR GROUP BY User.FIRST_NAME, User.SURNAME, User.EMAIL, User.USERID, Order.TIME_ORDERED, STAFF.STAFFID, Order.ORDER_TYPE Second option: SELECT DISTINCT User.USERID, User.FIRST_NAME, User.SURNAME, User.EMAIL, STAFF.STAFFID, MAX(DISTINCT Order.ORDERID), MAX(DISTINCT Order.ORDER_DATE), Order.ORDER_TYPE, MAX(DISTINCT Order.ORDER_PREP_TIME) AS MAX_FINISH, MIN(DISTINCT Order.ORDER_PREP_TIME) AS MIN_FINISH From Order INNER JOIN STAFF ON Order.STAFFID = STAFF.STAFFID INNER JOIN User ON STAFF.USERID = User.USERID WHERE MONTH(Order.ORDER_DATE) = SOME_MONTH AND YEAR(Order.ORDER_DATE) = SOME_YEAR GROUP BY User.FIRST_NAME, User.SURNAME, User.EMAIL, User.USERID, Order.TIME_ORDERED, STAFF.STAFFID, Order.ORDER_TYPE don't GROUP BY OrderID and Order_Date. These two columns in GROUP BY produce extra rows.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pinar avatar image pinar commented ·
Thank you for reply. First option returns multiple orders with the same order type and min and max preperation times are not added together it only shows max and min preparation time for particular order and not the order type. If I remove Order.TIME_ORDERED from GROUP BY it doesn't show multiple order of the same order type, but preparation times are still wrong, I think SUM function needs to be used but I might be wrong. Second option does the same what first option. Is there anyway to make it SUM(MAX(DISTINCT Order.ORDER_PREP_TIME)) AS MAX_FINISH and SUM(MIN(DISTINCT Order.ORDER_PREP_TIME)) AS MIN_FINISH ?
0 Likes 0 ·
pinar avatar image pinar commented ·
OMG! I am so blind hah, sorry, it actually works. First option works brilliant. Thank you very much!
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Not easy to give you a complete answer on this as I do not know the base tables, but you can use the windowed aggregate functions `min(...)over(...)` and `max(..)over(...)` select DISTINCT user.USERID, user.FIRST_NAME, user.SURNAME, user.EMAIL, STAFF.STAFFID, order.ORDERID, Order.ORDER_DATE, Order.ORDER_TYPE, min(order_prep_time)over(partition by user.USERID, user.FIRST_NAME, user.SURNAME, user.EMAIL, STAFF.STAFFID, order.ORDERID, Order.ORDER_DATE, Order.ORDER_TYPE) as MinPrep, max(order_prep_time)over(partition by user.USERID, user.FIRST_NAME, user.SURNAME, user.EMAIL, STAFF.STAFFID, order.ORDERID, Order.ORDER_DATE, Order.ORDER_TYPE) as MaxPrep FROM ....
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pinar avatar image pinar commented ·
Thanks for reply, unfortunately I am working with Derby and it doesn't support "partition by". Is there any other way to do it?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
ah it wasn't tagged as such and given that this is predominantly a SQL server forum, I presumed.... I've tagged your question. Try changing your GROUP BY to GROUP BY user.USERID, user.FIRST_NAME, user.SURNAME, user.EMAIL, STAFF.STAFFID, order.ORDERID, Order.ORDER_DATE, Order.ORDER_TYPE
0 Likes 0 ·
pinar avatar image pinar commented ·
Reordering the GROUP BY doesn't seem to be making any change. Wouldn't summing the MIN and MAX values make it work? Because eventually it has to sum up all minimum times and sum all the maximum times. I am not sure how to accomplish that since SUM(MAX) and SUM(MIN) doesn't seem to be allowed.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
I've not simply re-ordered the GROUP BY, I've changed the columns. Can you post some example data and the expected output - it's getting difficult to understand what the requirement is without seeing the raw data
0 Likes 0 ·
pinar avatar image
pinar answered
[DDL REMOVED] I can say what's happening: Once the staff finish making an order, the time it took him to do it will be stored in ORDER_PREP_TIME. I want to get the minimum time it took him to make all breakfast, all lunch and all dinner orders and maximum time it took him to make all breakfast, all lunch and all dinner.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.