select sort and group by

[maniacCow]

My database record is as below:-

cartid  foodid   qty
92      5        1
93      5        1
94      5        1
95      11       1
96      5        1
97      5        1

Can it be arrange into this form?
Assume that the max sum(qty)=3

foodid   sum(qty)
5        3
11       1
5        2

[gpl]

Are you saying that you want a query like:

Select foodid, Sum(qty)
from Mytable
Group by foodid

But that if the quantity exceeds 3 you want to show a new grouping ?
It is possible, but why ?

[maniacCow]

this method i had tried. Couldn't help.
The reason quantity set to 3 is because i limit the chief to cook the food limit to 3 quantities only...

[Jerry Winston]

I'm intrigued by your data requirement.

Can you elaborate on the purpose of the other fields?

• Is one Order(107) a group of servings (1,1,1,1,1) of a particular type of food? (5,5,5,11,5)?
• What purpose does cartid serve? What does it track?
• Will the qty field always be 1--since you don't record the infinite 0 servings served and each serving gets it's own entry?
• Is the purpose of this query to find instances where cooks prepared more than 3 servings at a time?

[maniacCow]

Sorry for that. You can ignore the orderid.
Foodid is the food type.
Yes. The quantity always will be 1.
Yes. The purpose of this query to find instances where cooks prepared more than 3 servings at a time

[gpl]

this solution makes use of a tally table

define a tally table

IF EXISTS (SELECT *
           FROM   Dbo.Sysobjects 
           WHERE  Id = Object_id(N'[dbo].[Tally]') 
                  AND Objectproperty(Id, N'IsUserTable') = 1) 
  DROP TABLE [dbo].[Tally]
GO 

CREATE TABLE [dbo].[Tally] 
  ( 
     [TallyNum] [INT] NOT NULL 
  ) 
ON [PRIMARY]
GO 

ALTER TABLE [dbo].[Tally] ADD CONSTRAINT [PK_Tally] PRIMARY KEY CLUSTERED ( 
[TallyNum] ) WITH FILLFACTOR = 100 ON [PRIMARY]
GO 

--- Fill table with all values

Insert Into [dbo].[Tally] ([TallyNum]) Values (0)
Insert Into [dbo].[Tally] ([TallyNum]) Values (1)
Insert Into [dbo].[Tally] ([TallyNum]) Values (2)
Insert Into [dbo].[Tally] ([TallyNum]) Values (3)
Insert Into [dbo].[Tally] ([TallyNum]) Values (4)
Insert Into [dbo].[Tally] ([TallyNum]) Values (5)
Insert Into [dbo].[Tally] ([TallyNum]) Values (6)
Insert Into [dbo].[Tally] ([TallyNum]) Values (7)
Insert Into [dbo].[Tally] ([TallyNum]) Values (8)
Insert Into [dbo].[Tally] ([TallyNum]) Values (9)

now query utilising the tally table

DECLARE @MAXCOUNT INT
SET @MAXCOUNT = 3

--- pull out the data
SELECT Foodid, 
       @MAXCOUNT 
FROM   ( (SELECT Foodid,
               Sum_qty / @MAXCOUNT AS Count_sum_qty 
        FROM   (SELECT Foodid, 
                       SUM(Qty) AS Sum_qty 
                FROM   Mytable 
                GROUP  BY Foodid) S1 
        WHERE  S1.Sum_qty / @MAXCOUNT > 0) s2
         JOIN (SELECT DISTINCT CONVERT(INT, 
                                             CONVERT(VARCHAR(1), T1.Tallynum) +
                                             CONVERT(VARCHAR(1), T2.Tallynum) + 
                                             CONVERT(VARCHAR(1), T3.Tallynum)
                                      ) AS Numbervalues
               FROM   Dbo.Tally T1, 
                      Dbo.Tally T2, 
                      Dbo.Tally T3) A 
           ON A.Numbervalues < Count_sum_qty -- tally count starts at 0, so not LE
       )
UNION ALL 
--- find all the remainders (where the sum is less than the max value allowed) 
SELECT Foodid, sum_qty % @MAXCOUNT
FROM 
(
  SELECT Foodid,
         SUM(Qty) as sum_qty
  FROM   Mytable
  GROUP  BY Foodid
  HAVING SUM(Qty) % @MAXCOUNT BETWEEN 1 AND @MAXCOUNT
) s2

you would need to wrap the above query into a stored proc, to allow for the variable maxcount.

If you are sure the chefs limit is 3, then extract the query from after the 'pull out the data comment'
and replace @MAXCOUNT with the real maximum, 3

good luck
Graham

[ck9663]

If you're just trying to see which foodid was prepared three or more times, would a:

Select o.foodid, f.foodname, count(*)
from Mytable o
left join YourFoodTable f on o.foodid = f.foodid
Group by o.foodid, f.foodname
having count(*) > 2

work?

The left join was thrown in to identify the food name. That is if you have a table for your food containing the description, etc...

Good Luck!!!

~~ CK

[maniacCow]

Not working... It just show 2 rows only.

GPL: I still trying on your solution ;) It quite new to me. I trying on it. Thx lots

[Jerry Winston]

@ maniacCow
It only shows two row because you asked us to discard orderID and cartid as unimportant so your data set looks like this?:

foodid   qty
5        1
5        1
5        1
11       1
5        1
5        1

If this is true, I'm quite positive it is impossible to calculate how many servings a cook prepared in a session.
Why:
There is no data available to define the concept of a "cooking session". The table above only tells you that you cooked n foods of type x. The qty column is unnecessary as each row,by design, asserts that 1 and only 1 serving of x was created as you stated above. Therefore, the only data we have to go on is this:

foodid  
5        
5        
5        
11       
5        
5

We cannot rely on the INSERT sequence of the rows to establish cooking sessions because we have nothing to ORDER BY.

I may have missed interpreted your requirement or it's over my head. I'll need more data in order to do any meaningful analysis.

[gpl]

Just say if you want me to talk you through it
Graham

[Jerry Winston]

@gpl

You do realize your solution doesn't record instances where the cook prepared more than 3 servings right? Per user requirement stated in maniacCow's post #5 you'd need to modify your solution to find the per instance count of servings prepared by the cook then find the instances greater than 3. The problem with your solution, is that it erroneously reports a chef that cooks 2 servings of x then cooks another 4 servings of X as having prepared 3 servings of x twice.


If I'm still failing to see your solution please do talk me through this.

[gpl]

Jerry
In post #3, maniacCow stated that it was a business rule that 3 was the limit that the chief (chef?) was allowed.

I dont believe the order of insertion is relevant (Id hope not as it is meaningless in a table).

If the cartid does determines a sequence, then you are right, my solution would not work in the scenario that you describe and a cursor or client-side application would have to do the calculation.

It does assume that the data is clean and no error has allowed the business rule to be broken.

Looking through the posts, post #5 says the qty will always be 1, you could use the presence of the record to indicate this and do a count instead of a sum.

I realise that in my long query, the line

HAVING SUM(Qty) % @MAXCOUNT BETWEEN 1 AND @MAXCOUNT

could more sensibly be replaced with

HAVING SUM(Qty) % @MAXCOUNT > 0

This is the trouble with trying to solve an issue when the complete problem domain is not described, and thus incorrect or incomplete solutions are given.

[Jerry Winston]

@mainiacCow

Can you share with us what is the purpose of the cartid field? Can you confirm this requirement from post #5?:

"The purpose of this query to find instances where cooks prepared more than 3 servings at a time"

@gpl

You're right, it's very tough to hit a moving target on-the-fly with a piece of code on the first. I thought I was over looking something.

[NeoPa]

maniacCow:
Sorry for that. You can ignore the orderid.

I would guess that this comment is unreliable for two reasons.

1. OrderID has never been introduced into the conversation in the first place. I suspect the reasonable guess that they were intending to refer to CartID is absolutely correct, however it does indicate a lack of precision and, by inference, a lack of appreciation for any more complicated detail.
2. From 1 above, and also the general level of understanding exhibited in the replies, I would say this statement was made due to a lack of appreciation of where that data might fit in to a solutioon.

I would say the CartID is actually the data required to determine which order the servings appear in, and should certainly be used as part of any workable solution.