Using column name as parameter and using input parameter in WHERE/HAVING clause

Question

I'm trying to get the number of an item based on a boolean field. The boolean field is a dynamic field passed as a parameter (as demonstrated in this post) during runtime.

EDIT: Finally solved it. Just changed the WHEN lines to: WHEN 'SI_A' THEN COUNT(CASE WHEN SI_A=1 THEN 1 END). Thanks @FDavidov!

    SELECT
        OrganismName
        , MONTH(DateDiscovered) AS 'Month'
        , CASE @Column
            WHEN 'SI_A' THEN COUNT(CASE WHEN SI_A=1 THEN 1 END)
            WHEN 'SI_B' THEN COUNT(CASE WHEN SI_B=1 THEN 1 END)
            WHEN 'SI_C' THEN COUNT(CASE WHEN SI_C=1 THEN 1 END)
            WHEN 'SI_D' THEN COUNT(CASE WHEN SI_D=1 THEN 1 END)
        END AS 'Cases'
    FROM tblFindings
    INNER JOIN
        tblOrganism
    ON
        tblFindings.OrganismID = tblOrganism.OrganismID
    WHERE
        (SELECT 
            CASE @Column
            WHEN 'SI_A' THEN COUNT(CASE WHEN SI_A=1 THEN 1 END)
            WHEN 'SI_B' THEN COUNT(CASE WHEN SI_B=1 THEN 1 END)
            WHEN 'SI_C' THEN COUNT(CASE WHEN SI_C=1 THEN 1 END)
            WHEN 'SI_D' THEN COUNT(CASE WHEN SI_D=1 THEN 1 END)
        END AS 'Cases'
        FROM tblFindings
        INNER JOIN
            tblOrganism
        ON
            tblFindings.OrganismID = tblOrganism.OrganismID
        ) > 0
    GROUP BY
        OrganismName
        , MONTH(DateDiscovered)
    ORDER BY
        OrganismName
        , MONTH(DateDiscovered)

EDIT: Forgot to include the full table. I tried using UNPIVOT but I'm getting the error: 'OrganismID was specified multiple times in u'. This one does not pass the column name as a parameter however:

SELECT
    OrganismName
    , MONTH(DateDiscovered) AS 'Month'
    , u.Cases
    , u.Sites
FROM tblFindings
INNER JOIN tblOrganism ON tblFindings.OrganismID = tblOrganism.OrganismID
UNPIVOT (
    Cases FOR Sites in (SI_A, SI_B, SI_C, SI_D) 
    ) u
WHERE u.Cases > 0

Table structure/sample data

Using this code, I'm also getting rows that have a total of 0. This is the result that I'm trying to achieve:

=========================
|OrganismName|Month|SI_A|
=========================
|nota        |2    |2   |
|something   |10   |1   |
|woo         |2    |1   |
=========================

=========================
|OrganismName|Month|SI_B|
=========================
|something   |4    |1   |
|something   |10   |1   |
=========================

=========================
|OrganismName|Month|SI_C|
=========================
|something   |10   |2   |
=========================

=========================
|OrganismName|Month|SI_D|
=========================
|something   |10   |1   |
=========================

Instead, I'm getting this (if I feed "SI_A" as parameter):

==========================
|OrganismName|Month|Cases|
==========================
|nota        |2    |2    |
|something   |4    |0    |
|something   |10   |1    |
|woo         |2    |1    |
|woo         |7    |0    |
==========================

I used a WHERE clause but got the result above. Then I tried a HAVING clause, but I got the same results. I'm not too familiar with SQL aside from doing simple queries, but how would I be able to achieve this?

Answer 1

I'm not sure what is reflected in your presented results, but here is a hint to an obvious error (and from here you may be able to resolve your problem):

SELECT 
   OrganismName 
   MONTH(DateDiscovered) AS 'Month' 
 , CASE @Column 
      WHEN 'SI_A'   THEN SUM(CASE WHEN SI_A=1 THEN 1 ELSE 0 END) 
      WHEN 'SI_B'   THEN SUM(CASE WHEN SI_B=1 THEN 1 ELSE 0 END) 
      WHEN 'SI_C'   THEN SUM(CASE WHEN SI_C=1 THEN 1 ELSE 0 END) 
      WHEN 'SI_D'   THEN SUM(CASE WHEN SI_D=1 THEN 1 ELSE 0 END) 
      ELSE NULL 
 END AS 'Cases' 
FROM tblFindings 
....

You are checking if (for instance) SI_A is 1 to add 1 otherwise 0. Now, what happens if SI_A = 2? You are also adding 0.

Since I don't quite understand if you what t0 SUM all the values or COUNT how many cases you have a value > 0, I'm posting here both cases:

For SUM:

... WHEN 'SI_A' THEN SUM(CASE WHEN SI_A > 0 THEN SI_A ELSE 0 END) ...

or

... WHEN 'SI_A' THEN SUM(SI_A) ...

(the second is equivalent assuming that you are only getting 0 or a number > 0).

For COUNT:

... WHEN 'SI_A' THEN SUM(CASE WHEN SI_A > 0 THEN 1 ELSE 0 END) ...

Hope this is the solution you were looing for.

UPDATE

Here is a single and simple select you can start from:

SELECT 
   OrganismName 
   MONTH(DateDiscovered) AS 'Month' 
 , CASE @Column 
      WHEN 'SI_A'   THEN SUM(CASE WHEN SI_A > 0 THEN 1 ELSE 0 END) 
      WHEN 'SI_B'   THEN SUM(CASE WHEN SI_B > 0 THEN 1 ELSE 0 END) 
      WHEN 'SI_C'   THEN SUM(CASE WHEN SI_C > 0 THEN 1 ELSE 0 END) 
      WHEN 'SI_D'   THEN SUM(CASE WHEN SI_D > 0 THEN 1 ELSE 0 END) 
      ELSE NULL 
 END AS @Column 
FROM tblFindings

I guess this will do it.