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.)
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.
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 ?
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 ....
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
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.
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
[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.
15 People are following this question.
