Sql query

Question

My database entry is as follows: -

cartid  orderid   foodid   qty
==============================
92      107       5        1
93      107       5        1
94      107       5        1
95      107       11       1
96      107       5        1
97      108       5        1

Could this be in this form?
Suppose the maximum sum (qty) = 3

foodid   sum(qty)
=================
5        3
11       1
5        2

Answer 1

It looks like you are trying to summarize adjacent ranges (islands) of the same foodidsorted by cartid?

;with cart as
(
SELECT 92 AS cartid,107 AS orderid,5 AS foodid, 1 AS qty UNION ALL
SELECT 93,107,5, 1 UNION ALL
SELECT 94,107,5, 1 UNION ALL
SELECT 95,107,11,1 UNION ALL
SELECT 96,107,5, 1 UNION ALL
SELECT 97,108,5, 1 

),
NumberedCart As
(
SELECT cartid,foodid,qty,
ROW_NUMBER() OVER (ORDER BY cartid)- 
       ROW_NUMBER() OVER (PARTITION BY foodid ORDER BY cartid) AS G
 FROM cart
 )

 SELECT foodid, SUM(qty) AS [sum(qty)]
 FROM NumberedCart
 GROUP BY foodid,G
 ORDER BY MIN(cartid)

Returns

foodid      sum(qty)
----------- -----------
5           3
11          1
5           2

Answer 2

I'm not sure what you mean by Max Sum (qty) = 3 ...

But here are some SQL to get you started:

SELECT        foodid
            , SUM(qty)
FROM        YourTableName
GROUP BY    foodid