sql query distinct

Question

My database record is as below:-

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

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

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

Answer 1

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

But here is some SQL to get you started:

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

Answer 2

You look like you are trying to sum contiguous ranges (islands) of the same foodid ordered 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