SQL Select case

Question

I have the following sql tables

oitems table

    +---------+-----------+----------+
    | orderid | catalogid | numitems |
    +---------+-----------+----------+
    | O737    |       353 |        1 |
    | O738    |       364 |        4 |
    | O739    |       353 |        3 |
    | O740    |       364 |        6 |
    | O741    |       882 |        2 |
    | O742    |       224 |        5 |
    | O743    |       224 |        2 |
    +---------+-----------+----------+

Orders table
+-----------------+------------+------------+
|         orderid | ocardtype  |   odate    |
+-----------------+------------+------------+
|     O737        | Paypal     |            | 'OK
|     O738        | MasterCard | 01.02.2012 | 'OK
|     O739        | MasterCard | 02.02.2012 | 'OK
|     O740        | Visa       | 03.02.2012 | 'OK
|     O741        | Sofort     |            | 'OK
|     O742        |            |            | 'ignore because ocardtype is empty
|     O743        | MasterCard |            | 'ignore because Mastercard no odate
+-----------------+------------+------------+

the reusltant datatable called result

 +-----------+----------+--------------+
| catalogid | numitems | ignoreditems |
+-----------+----------+--------------+
|       353 |        4 |            0 |
|       364 |       10 |            0 |
|       882 |        2 |            0 |
|       224 |        0 |            7 |
+-----------+----------+--------------+

idea is to sum the numitems column for products that have the same catalogid depinding on the data in the oitems table with the following conditions

if ocardtype is empty then ignore the numitems and consider it as 0 in the sum and sum the ignored items to the ignoreditems column
if ocardtype for some order is MasterCard or Visa and the odate is empty then ignore the numitems and consider it as 0 and sum the ignored items to the ignoreditems column
if ocardtype is Paypal or Sofort, then just do the numitems sum without checking the date because those types require no odate

basicly i want to save the result datatable to a temporary datatable and load it to a vb.net datatable

i am having a hard time figuring out how to do this in an sql query! i need this as sql command for vb.net , was able to do it programmatically using vb.net datatables using loops and alot of checking using linq is an option, but i just need to get this from the server

score 1 · Answer 1 · answered Oct 01 '12 at 06:53

Something like:

SELECT
     oi.catalog_id,
     SUM(CASE
            WHEN ocardtype in ('Paypal','Sofort') THEN numitems
            WHEN ocardtype in ('Mastercard','Visa') and odate is not null THEN numitems
            ELSE 0 END) as numitems,
     SUM(CASE
            WHEN ocardtype is null then numitems
            WHEN ocardtype in ('Mastercard','Visa') and odate is null THEN numitems
            ELSE 0 END) as ignoreditems
FROM
   oitems oi
      inner join
   Orders o
      on
         oi.orderid = o.orderid
GROUP BY
   oi.catalog_id

(Assuming that wherever you've used the word "empty" in your narrative, you mean the column is NULL)

I would -1 this for logic, but I'll let it pass. It reads like the OP wants the 2 sums to be complementary, but however I look at this, there will be cases where they won't add up to the total. — RichardTheKiwi, Oct 01 '12 at 07:02

RichardTheKiwi · Accepted Answer · 2012-10-01T08:51:48.390

1

select catalogid, numitems, allitems - numitems ignoreditems
from (
  select i.catalogid,
    sum(case when (ocardtype in ('PayPal','Sofort') OR
                   ocardtype in ('mastercard','visa') and
                   odate is not null) AND NOT EXISTS (
                     select * from booked b
                     where b.ignoredoid = o.orderid
                   ) then numitems
                   else 0 end) numitems,
    sum(numitems) allitems
  from orders o
  join oitems i on i.orderid=o.orderid
  group by i.catalogid
) X

edited Oct 01 '12 at 08:51

answered Oct 01 '12 at 06:54

RichardTheKiwi

105,798
26
196
262

The OP has referred to both `ocardtype` and `odate` as potentially being "empty" - yet you've interpreted that in *one* case to be "the empty string", and in another as "NULL". (Aha, sneaky edit I see :-)) – Damien_The_Unbeliever Oct 01 '12 at 06:56
@Damien - I use `col > ''` when I want to simultaneously exclude NULL and ''. Because it's easy and works. But yes, I've taken even that out since it falls into ELSE easily. – RichardTheKiwi Oct 01 '12 at 06:59
@Richard aka cyberkiwi one more thing please, in another table called `booked` i have a column called `ignoredoid`, this columns contains orderids from the above table that i want to ignore even if the conditions above are right, so how can i do this, something like `WHERE orderid does not exist in column ignoredoid of table booked` – user1570048 Oct 01 '12 at 08:15
@Richard aka cyberkiwi thank you very much, sql is really cool and i should start learning it! – user1570048 Oct 01 '12 at 08:58
@Richard aka cyberkiwi Hello, i've update the question, can you please check the last part? – user1570048 Oct 01 '12 at 11:37
You really should ask that as a new question instead of evolving a single question. I'm off to sleep so good luck with it. – RichardTheKiwi Oct 01 '12 at 11:42
@Richardakacyberkiwi i did! :) and still no luck http://stackoverflow.com/questions/12674363/sql-add-processed-ids-to-a-single-cell-seperated-with-a-comma – user1570048 Oct 01 '12 at 13:36

Mark Hurd · Answer 3 · 2015-10-13T13:03:41.853

Here is a Linq-to-sql version of your original request (as a LINQpad query):

Dim odateRequired = {"MasterCard", "Visa"}
Dim odateNotRequired = {"Paypal", "Sofort"}
Dim result = From o In Orders Join i In Oitems On o.orderid Equals i.orderid _
             Let check = o.ocardtype IsNot Nothing _
                     AndAlso ((odateRequired.Contains(o.ocardtype) _
                               AndAlso o.odate IsNot Nothing) _
                        OrElse odateNotRequired.Contains(o.ocardtype)) _
         Group By i.catalogid Into _
         numitem = Sum(If(check, i.numitems, 0)), _
         ignored = Sum(If(check, 0, i.numitems))

result.Dump

Your extra request in the comments to RichardTheKiwi's answer (it just includes Not (From b In Bookeds Where b.ignoredoid=i.orderid).Any AndAlso at the front of the check):

Dim odateRequired = {"MasterCard", "Visa"}
Dim odateNotRequired = {"Paypal", "Sofort"}
Dim result = From o In Orders Join i In Oitems On o.orderid Equals i.orderid _
             Let check = Not (From b In Bookeds Where b.ignoredoid = i.orderid).Any _
                     AndAlso o.ocardtype IsNot Nothing _
                     AndAlso ((odateRequired.Contains(o.ocardtype) _
                               AndAlso o.odate IsNot Nothing) _
                        OrElse odateNotRequired.Contains(o.ocardtype)) _
         Group By i.catalogid Into _
         numitem = Sum(If(check, i.numitems, 0)), _
         ignored = Sum(If(check, 0, i.numitems))

result.Dump

SQL Select case

3 Answers3

Linked