[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

SQL variable counter for when case criteria met

Hi,
Need help with be able to have variable counter for each row and count the criteria and have total criteria met in column.

Example.

DECLARE @Pcounter INT
SET @Pcounter =0

SELECT A,B,C,D,E

CASE WHEN A>.30 THEN @Pcounter = @Pcounter +1
        WHEN B>.4 THEN @Pcounter = @Pcounter +1
ELSE @Pcounter = 0
END
FROM Table 1

Error is = , I"m not sure how to fix this for rewrite to correct.
It needs to count on each line of the table and criteria total for each line.
thanks
0
SSschultz
Asked:
SSschultz
  • 4
  • 2
  • 2
1 Solution
 
tim_csCommented:
Not 100% clear on what you're after.  Mayb provide some sample data and expected result.  But maybe this is what you're trying to do?


SELECT
      @Pcounter = SUM(CASE WHEN A > .3 OR B > .4 THEN 1 ELSE 0 )
FROM
      Table1
0
 
lwadwellCommented:
do you mean?
SELECT A,B,C,D,E
      @Pcounter = SUM(CASE WHEN A>.30 THEN 1
                           WHEN B>.4  THEN 1
                           ELSE 0
                      END)
FROM Table 1
GROUP BY A,B,C,D,E

Open in new window

0
 
SSschultzAuthor Commented:
Here's some sample data.

A       B       C       D       E       F (expected count column >.5)
car   .40     .50   .60    .70     2
boat  .5      .2     .8      .1       1

So column F is calculating columns B-E and counting anything greater than .5
and expect result for car would be 2 (D=.6 and E .7) and for boat 1 (D=.8)

THis is for several thousand rows table.
thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tim_csCommented:
SELECT
   A,B,C,D,E,
   CASE WHEN A > .5 THEN 1 ELSE 0 END + CASE WHEN B > .5 THEN 1  ELSE 0 END + CASE WHEN C > .5 THEN 1 ELSE 0  END + CASE WHEN D > .5 THEN 1  ELSE 0 END + CASE WHEN E > .5 THEN 1 ELSE 0  END AS F
FROM
   Table1
0
 
SSschultzAuthor Commented:
lwadwell,
I've tried your solution and get error "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
0
 
SSschultzAuthor Commented:
tim_cs

WOrks Great.. thanks very much for your help!
0
 
SSschultzAuthor Commented:
tim_cs,

Thanks for your fast and accurate solution.
Excellent solution.
0
 
lwadwellCommented:
It didn't suit what your later comment clarified anyway.

using ROUND() ... but subtracting 0.001 to ensure 0.5 rounds down ... needs to be small enough to ensure a number just over .5 doesn't get rounded down (so make it smaller that your precision)
SELECT A,B,C,D,E,
       round(A-0.001,0)+round(B-0.001,0)+round(C-0.001,0)+round(D-0.001,0)+round(E-0.001,0) F
FROM Table 1
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now