Another simple SQL question

Question

Hello everyone,

i have this query and count(artikel.artikelid) returns 2 instead of 3. i know why, because i have the artikelid twice (8135) in the 'where in...' part but i would like it to return 3

here is my problem query:

SELECT    cg.CategorieGroepID, 
          cg.GroepsNaam, 
          cg.BenodigdAantal, 
          cg.ActieCatId, 
          cg.ActieActief, 
          count(Artikel.ArtikelID)
FROM      CategorieGroep cg INNER JOIN
          Categorie ON cg.CategorieGroepID = Categorie.CategorieGroepID INNER JOIN
                  Artikel ON Categorie.CategorieID = Artikel.CategorieID
                  where artikel.artikelid in(8135,8135,7914)
group by  cg.CategorieGroepID, 
          cg.GroepsNaam, 
          cg.BenodigdAantal, 
          cg.ActieCatId, 
          cg.ActieActief

too clear things up, the where artikel.artikelid in(8135,8135,7914) part is meant to be used later for a dynamic array of artikelid's

people can have the same artikelid twice and then the query should return 2 instead of one. i am sorry if my question is not totally clear.

it is meant for building an overview of a users shoppingbasket. people can order artikelid 8135 (for instance) twice or even more and the query should return the amount of products by main categorie

Answer 1

Note that it doesn't matter how many times you have a value in the IN clause list, it will still only match once per row.

So the part about you having 8135 twice in the list, that's not the reason you get 2 or 3 or whatever. Internally it will count as one unique value to match against.

Answer 2

It will only return 2 because one of them matches the (redundant) condition "in (8135, 8135...".

Answer 3

If there aren't actually 2 records with the artikelid of 8135 it won't return 3. Why not just do

SELECT cg.CategorieGroepID, cg.GroepsNaam, cg.BenodigdAantal, cg.ActieCatId, cg.ActieActief, 3?

Answer 4

If you know you want that column to return 3, you can end the select clause with "3" instead of count(Artikel.ArtikelID).

Answer 5

In order to generate three records, you need to join your table with another table that contains three records. Some SQL database systems allow you to specify this as a subquery, to specify code or to make a memory table that contains (8135,8135,7914), so that you can join it. But if your SQL engine is very simple, you actually need to insert it into a real table.

However, I assume that these numbers come from program code, so if you program source code knows the count of numbers, why not use that, instead?

Third solution: If your SQL database supports union inside subselects, then you can replace your "join artikel" with "join (select ... from artikel where artikelid=8135 ... union select ... from artikel where artikelid=8135 ... union select ... from artikel where artikelid=7914).

Answer 6

Can't you add a rowindex column to have consecutive values and then count for this column?

Answer 7

You can put the value 8135 into the In ( ... ) clause as many times as you want and it will not affect how many rows are returned with that value, the Count(ArtikelID) should be the same even if it is listed 10 times. If the query is returning '2', then that means there are 2 rows in the returned rowset... One row with value 8135, and one row with value 7914.

What exactly are you trying to count wit hthe value of '3' that you are trying to ghet the query to produce? The number of entries in your In Clause ? or something related to the data in the table?

If you are trying to get the row with value 8135 to be counted twice, You can't do that by listed the value twice in the In clause.

Answer 8

SELECT    cg.CategorieGroepID, 
      cg.GroepsNaam, 
      cg.BenodigdAantal, 
      cg.ActieCatId, 
      cg.ActieActief, 
      count(a.ArtikelID),
      case cg.BenodigdAantal 
      when 0 then 0 else count(a.ArtikelID)/cg.BenodigdAantal end as gratisart
FROM      CategorieGroep cg INNER JOIN
      Categorie ON cg.CategorieGroepID = Categorie.CategorieGroepID INNER JOIN
              Artikel a ON Categorie.CategorieID = a.CategorieID,
               (SELECT artikelid FROM Artikel WHERE artikelid=8135 UNION ALL
       SELECT artikelid FROM Artikel WHERE artikelid=8135 UNION ALL
       SELECT artikelid FROM Artikel WHERE artikelid=8135 UNION ALL
       SELECT artikelid FROM Artikel WHERE artikelid=7600 UNION ALL
       SELECT artikelid FROM Artikel WHERE artikelid=7914)  b
       where a.artikelid = b.artikelid
group by  cg.CategorieGroepID, 
      cg.GroepsNaam, 
      cg.BenodigdAantal, 
      cg.ActieCatId, 
      cg.ActieActief