Solution for SQL conditional query

Question

I've been tasked with coming up with a solution for a problem that was found this morning. I have a query that I need to do some math with. I have three pertinent columns.

SELECT lQ.[QUANTITY], lQ.[FORM_FACTOR_ID], oQ.[INDIVIDUAL_PACKAGING] 
FROM [dbo].[AOF_ORDER_LINE_QUEUE] as lQ
LEFT JOIN [dbo].[AOF_ORDER_QUEUE] AS oQ
  ON lQ.[SALES_ORDER_NUMBER] = oQ.[SALES_ORDER_NUMBER]

I can see myself doing this in a loop easily in languages I know best. It doesn't seem that looping is a good thing to do in SQL based on some preliminary research so I am reaching out for suggestions.

I need to output a total value which is a conditional sum of lQ.[QUANTITY]. The condition is if oQ.[FORM_FACTOR_ID] is equal to 1 then the output for that particular row is equal to the value of lQ.[QUANTITY]. If oQ.[FORM_FACTOR_ID] is equal to 2 then if oQ.[INDIVIDUAL_PACKAGING] is true, then the output of that particular row in the query is equal to lQ.[QUANTITY]. If the value is false, then the output of that particular row in the query is divided by 2. The final output needs to be a single integer.

QUANTITY    FORM_FACTOR_ID  INDIVIDUAL_PACKAGING
4           2               1
5           1               1

I would need a query that outputs the value 7 for the above table.

QUANTITY    FORM_FACTOR_ID  INDIVIDUAL_PACKAGING
4           2               0
5           2               0

That same query needs to output 5 for the above table.

What would be the best way to go about doing this?

using Case in your select?

TechGirl
– TechGirl

2017-09-20 16:06:24 +00:00
Commented Sep 20, 2017 at 16:06 — TechGirl
– TechGirl, Commented Sep 20, 2017 at 16:06
Kudos for realizing a loop was a bad idea.

Xedni
– Xedni

2017-09-20 16:10:11 +00:00
Commented Sep 20, 2017 at 16:10 — Xedni
– Xedni, Commented Sep 20, 2017 at 16:10

Gordon Linoff · Accepted Answer · 2017-09-21 22:47:46Z

3

If I understand the question correctly, you just want conditional aggregation -- a CASE as an argument to SUM().

If I follow the logic, it would look like:

SELECT SUM(CASE WHEN oq.FORM_FACTOR_ID = 1 THEN lQ.QUANTITY
                WHEN oQ.FORM_FACTOR_ID = 2 AND oQ.INDIVIDUAL_PACKAGING = 1 THEN lQ.QUANTITY
                WHEN oQ.FORM_FACTOR_ID = 2 AND oQ.INDIVIDUAL_PACKAGING = 0 THEN lQ.QUANTITY / 2
            END)
FROM [dbo].[AOF_ORDER_LINE_QUEUE] lQ LEFT JOIN
     [dbo].[AOF_ORDER_QUEUE] oQ
     ON lQ.[SALES_ORDER_NUMBER] = oQ.[SALES_ORDER_NUMBER];

edited Sep 21, 2017 at 22:47

answered Sep 20, 2017 at 16:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

addohm Over a year ago

There seems to be only one condition in which this doesn't behave as expected. When INDIVIDUAL_PACKING is True and the QUANTITY total is odd, it doesn't round up. I'll update the main post with this part.

addohm Over a year ago

I've added CEILING(lQ.[QUANTITY] / 2) to the third case which should address my odd numbers problem, but I'm still getting 4 (the sum total). Any ideas?

addohm Over a year ago

@Simon if you read the comment just above yours you'll see I did that already. The solution is CEILING(lQ.[QUANTITY] / 2.0) as 2 by itself is of the integer data type and 2.0 is of the decimal data type.

addohm Over a year ago

@Simon I'm not sure what you're getting at, but again you're trying to post on top of the solution I've already posted. The problem isn't that lQ.[QUANTITY] is an integer. The problem is because I'm dividing two integers, the resulting solution outputs an integer. If you change either of the two to a decimal, the answer becomes a decimal, which in turn becomes number I can either ROUND, CEILING, or FLOOR in the way needed.

Collectives™ on Stack Overflow

Solution for SQL conditional query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related