C# Group with conditional

Question

how can i grouping a data with conditional if bill < 10 ?

i have table:

meetingId | bill
------------------
     a    |     6
     b    |     7
     c    |     1
     a    |     5
     a    |     3
     b    |     4
     g    |     2

expected results : a = 6+5+3 = 14 limit is 10 --> 10 and 4
b = 7+4 = 11 so limit is 10 --> 10 and 1
c and g not over the limit.

meetingId | bill
------------------
     a    |     10
     a    |     4
     b    |     10
     b    |     1
     c    |     1
     g    |     2

i tried in SQL why but i stuck with if condition

my SQL :

 SELECT NO_ORDRE
      ,ORDRE.CODE_CLIENT As CodeCl 
      ,[CODE_DEST]  
      ,ORDRE.RS_NOM As OrdreRS
      ,ORDRE.ADRESSE As OrdreAdr
      ,ORDRE.CP As OrdreCP
      ,ORDRE.VILLE As OrdreVille
      ,ENLEV_CREMB
      ,ENLEV_DECL
      ,MODAL_MODE
      ,[PAYS]
      ,[INSEE]
      ,[SIRET]
      ,ORDRE.TEL As OrdreTel
      ,ORDRE.FAX As OrdreFax
      ,[EMAIL]     
      ,[NBR_COLIS]
      ,[POID]   
      ,[OBS]
      ,[DATE_CREE]
      ,[DATE_MODIF]
      ,[REF_EXPED]
      ,[AUTRE_REF]
      ,[AGENCE]
      ,[TRANSPORTEUR]
      ,NOM
      ,CAPITAL
      ,LIBELLE
      ,T_LOGO.IMG As FaImg
      ,T_LOGO.ADRESSE As FaAdr
      ,T_LOGO.CP As FaCp
      ,T_LOGO.VILLE As FaVille
      ,T_LOGO.TEL As FaTel
      ,T_LOGO.FAX As FaFax
      ,FAWEB_CLIENT.RS_NOM As CliRsNom
      ,FAWEB_CLIENT.ADRESSE As CliAdr
      ,FAWEB_CLIENT.CP As CliCp
      ,FAWEB_CLIENT.VILLE As CliVille
  FROM [ORDRE]
  LEFT JOIN T_LOGO ON ORDRE.TRANSPORTEUR = T_LOGO.NOID
  LEFT JOIN FAWEB_CLIENT ON ORDRE.CODE_CLIENT = FAWEB_CLIENT.CODE_CLIENT
 WHERE (STATUT_ORDRE = 2) AND (TRANSPORTEUR IN (SELECT ParsedString From dbo.ParseStringList(@Trans)))

and then i use in C#

List<Pers_Ordre> oListOrdre = new List<Pers_Ordre>();
                while (readerOne.Read())
                {
                   Pers_Ordre oPerOrdr = new Pers_Ordre();
                   Pers_Ordre test =  (from t in oListOrdre where t.DestId == readerOne["CODE_DEST"].ToString() select t).FirstOrDefault();


                    oPerOrdr.OrdreId = Convert.ToInt32(readerOne["NO_ORDRE"]);
                    oPerOrdr.DestId = readerOne["CODE_DEST"].ToString();

                    if (test == null)
                    {
                        oListOrdre.Add(oPerOrdr);
                    }
                    else
                    {
                        int NbrColis = (from t in oListOrdre where t.DestId == readerOne["CODE_DEST"].ToString() select t.NbrColis).FirstOrDefault();
                        if (NbrColis < 5)
                        {
                            test.NbrColis += NbrColis;
                        }
                    }
                }

it not work what i expected.

Thanks for your help!

What version of .NET are you using? Can you use LINQ? – Oded Nov 23 '11 at 15:34 — Oded, Nov 23 '11 at 15:34
Thanks you for responding. i use the last version of .NET – user609511 Nov 23 '11 at 15:34 — user609511, Nov 23 '11 at 15:34

dtb · Accepted Answer · 2011-11-23T15:56:18.930

^{(Not really an answer, but this doesn't fit in a comment.)}
Here's a LINQ-to-Objects query that groups items by meetingId and creates new items such that there is one item with bill less than 10 and as many items as needed with bill equalling 10 to keep the sum:

Screenshot

Is this what you're looking for?

_Code:

var list = new List<Tuple<char, int>>
{
    Tuple.Create('a', 6),
    Tuple.Create('b', 7),
    Tuple.Create('c', 1),
    Tuple.Create('a', 5),
    Tuple.Create('a', 3),
    Tuple.Create('b', 4),
    Tuple.Create('g', 2),
};

var result = list
    .GroupBy(x => x.Item1)
    .Select(g => new
    {
        Key = g.Key,
        Sum = g.Sum(x => x.Item2)
    })
    .Select(p => new
    {
        Key = p.Key,
        Items = Enumerable.Repeat(10, p.Sum / 10)
                          .Concat(Enumerable.Repeat(p.Sum % 10, 1))
    })
    .SelectMany(p => p.Items.Select(i => Tuple.Create(p.Key, i)))
    .ToList();

JonH · Answer 2 · 2011-11-23T15:42:11.287

1

You should not do this at the client side because it can get pretty intensive, a simple GROUP BY with a HAVING clause should give you the expected results:

Sample data:

enter image description here

The query you need:

SELECT 
    MeetingID, 
    SUM(bill) AS Total
FROM 
    Table_1 
GROUP BY 
    MeetingID 
HAVING 
    SUM(bill) < 10

The results of the query:

enter image description here

edited Nov 23 '11 at 15:42

answered Nov 23 '11 at 15:35

JonH

32,732
12
87
145

Thanks you, but i need a--10 and a--2, because a = 5+4+3 = 12, c--10 and c--2, because c = 11+1=12, d--10 and d--2, because d = 12 – user609511 Nov 23 '11 at 19:06
I am just using my sample data. – JonH Nov 23 '11 at 19:17

score 1 · Answer 3 · answered Nov 23 '11 at 15:36

1

This SQL query will return the wanted results:

SELECT meetingId, SUM(bill) as bill_total
FROM table
GROUP BY meetingId
HAVING SUM(bill) < 10

answered Nov 23 '11 at 15:36

Oded

489,969
99
883
1,009

score 1 · Answer 4 · answered Nov 23 '11 at 15:50

1

table.GroupBy(p => p.meetingId).Where(p => p.Sum(q => q.bill) < 10)
.Select(p => new
{
    meetingId= p.Key,
    bill= p.Sum(q => q.bill)
});

answered Nov 23 '11 at 15:50

Reza ArabQaeni

4,848
27
46

C# Group with conditional

4 Answers4