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
13 2717
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 ?
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...
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?
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
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
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
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
@ 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:
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.
Just say if you want me to talk you through it
Graham
@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.
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.
@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 32,556
Expert Mod 16PB maniacCow:
Sorry for that. You can ignore the orderid.
I would guess that this comment is unreliable for two reasons. - 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.
- 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: James P. |
last post by:
Hello,
I have a report with the Priority field is used as sort order and
grouping. The problem is the data in this Priority field if sorted in
ascending order is: High, Low, and Medium. How...
|
by: Gerrit Beuze |
last post by:
Hi all,
I'm wondering if you how you organize as (in sorting / order) your C# class code:
Do you sort/ group by member type: fields, methods, properties etc.?
If yes: what ordering scheme do...
|
by: Thomas |
last post by:
Hi!
I´m a newbie with this and I´m trying to build a forum of my own but
have stumbled on my first problem as early as the opening page.
To the problem:
I want to show a simple forum layout...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: Jim Andersen |
last post by:
I have this xml-file. I want to sort it, and create a new xml-file. The
result should be identical to the input except that it's sorted.
<?xml version="1.0" encoding="UTF-8"?>
<levelone>
<child...
|
by: zafm86 |
last post by:
Hi everyone!
I'm sure my problem is pretty easy to solve but I've been working on it for a long and my my brain is not working correctly anymore.
I'm working with an AS400 and I mhave to do an...
|
by: monicaj |
last post by:
Hi,
Can anyone suggest - what are the alternative means I achieve following functionality -
select x , y from table1 group by x
since rules say group by clause needs all listed fields in...
|
by: inamul |
last post by:
I want to select CheckBox based on data retrieved from mysql database table.
Could anyone show me the simple way of doing it.
Data store in table under colum "sectionOfInterest" is shown below...
|
by: =?Utf-8?B?UmF2aSBTaGFua2Fy?= |
last post by:
Hi,
Is it possible to popup the User/Goup Selection dialog box as part of my
application (VB.Net, VS2005) ? I wish to permit the user to select/assign
existing User Accounts (local computer or...
|
by: dantebothermy |
last post by:
Hi all,
I have a form with a subform. On the form is a combo box with three choices "current", "former" and "all". I'd like to use that combo box to set the data source for the subform based on...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |