How to count distinct values?

1

I have some horrible, hard-coded SQL that was done before I got here.

The model numbers have been updated, and I have been tasked with updating 10 lengthy SQL commands so that they work with the models that are in our database.

The column is "DAMDLN" (abbreviation for something like Data Access MoDeL Number).

This is an IBM AS400 database, so I cannot use Microsoft SQL tricks.

Also, the statement has to be able to be called from C# code in Visual Studio.

        private string ModelSelectSQL = "SELECT COUNT(CASE WHEN DAMDLN = '200S' THEN DAMDLN ELSE NULL END) AS B200S,"
+ "COUNT(CASE WHEN DAMDLN = '220S' THEN DAMDLN ELSE NULL END) AS B220S, "
+"COUNT(CASE WHEN DAMDLN = 'CS1' THEN DAMDLN ELSE NULL END) AS BCS1, "
+"COUNT(CASE WHEN DAMDLN = 'CS2' THEN DAMDLN ELSE NULL END) AS BCS2, "
+"COUNT(CASE WHEN DAMDLN = 'CS3' THEN DAMDLN ELSE NULL END) AS BCS3, "
+"COUNT(CASE WHEN DAMDLN = 'R3' THEN DAMDLN ELSE NULL END) AS BR3, "
+"COUNT(CASE WHEN DAMDLN = 'R5' THEN DAMDLN ELSE NULL END) AS BR5, "
+"COUNT(CASE WHEN DAMDLN = 'R7' THEN DAMDLN ELSE NULL END) AS BR7, "
+"COUNT(CASE WHEN DAMDLN = 'R30' THEN DAMDLN ELSE NULL END) AS BR30, "
+"COUNT(CASE WHEN DAMDLN = 'R35' THEN DAMDLN ELSE NULL END) AS BR35, "
+"COUNT(CASE WHEN DAMDLN = 'C3S' THEN DAMDLN ELSE NULL END) AS BC3S, "
+"COUNT(CASE WHEN DAMDLN = 'R3W' THEN DAMDLN ELSE NULL END) AS BR3W, "
+"COUNT(CASE WHEN DAMDLN = 'R5W' THEN DAMDLN ELSE NULL END) AS BR5W, "
+"COUNT(CASE WHEN DAMDLN = 'R7W' THEN DAMDLN ELSE NULL END) AS BR7W, "
+"COUNT(CASE WHEN DAMDLN = '3WS' THEN DAMDLN ELSE NULL END) AS B3WS, "
+"COUNT(CASE WHEN DAMDLN = '5WS' THEN DAMDLN ELSE NULL END) AS B5WS, "
+"COUNT(CASE WHEN DAMDLN = '7WS' THEN DAMDLN ELSE NULL END) AS B7WS, "

+"COUNT(CASE WHEN DAMDLN IN ('23SC','SC23') THEN DAMDLN ELSE NULL END) AS B23SC, "
+"COUNT(CASE WHEN DAMDLN IN ('25SC','SC25') THEN DAMDLN ELSE NULL END) AS B25SC, "
+"COUNT(CASE WHEN DAMDLN = '30SC' THEN DAMDLN ELSE NULL END) AS B30SC, "
+"COUNT(CASE WHEN DAMDLN = 'A28' THEN DAMDLN ELSE NULL END) AS BA28, "
+"COUNT(CASE WHEN DAMDLN = 'A36' THEN DAMDLN ELSE NULL END) AS BA36, "

+"COUNT(CASE WHEN DAMDLN = 'A4C' THEN DAMDLN ELSE NULL END) AS BA4C, "
+"COUNT(CASE WHEN DAMDLN = 'A29' THEN DAMDLN ELSE NULL END) AS BA29, "
+"COUNT(CASE WHEN DAMDLN = 'R6' THEN DAMDLN ELSE NULL END) AS BR6, "
+"COUNT(CASE WHEN DAMDLN = 'R8' THEN DAMDLN ELSE NULL END) AS BR8, "
+"COUNT(CASE WHEN DAMDLN = '6WS' THEN DAMDLN ELSE NULL END) AS B6WS, "
+"COUNT(CASE WHEN DAMDLN = '8WS' THEN DAMDLN ELSE NULL END) AS B8WS, "
+"COUNT(CASE WHEN DAMDLN = 'R6B' THEN DAMDLN ELSE NULL END) AS BR6B, "
+"COUNT(CASE WHEN DAMDLN = 'R8B' THEN DAMDLN ELSE NULL END) AS BR8B, "
+"COUNT(DAMDLN) AS MDLCOUNTALL, "
+"COUNT(CASE WHEN DAMDLN NOT IN('200S','220S','CS2','CS3','R3','R5','R7','R30','R35','C3S','3WS','5WS','7WS','23SC','25SC','SC23','SC25','30SC','A29','A36','A36B','A4C') THEN DAMDLN ELSE NULL END) AS Other ";
2

What, specifically, is your q? The code is not efficient but it should work. (Better would be:
SUM(CASE WHEN DAMDLN = 'whatever' THEN 1 ELSE 0 END) AS whatever,

3

The model numbers have changed.

Is there a way to write this SELECT query so that the model numbers are dynamically populated, or do I have to go back in to each of the 10 SQL commands and manually change them to the current list of models available?

If I hard code it, that will only work until they introduce another model or until one of the models is discontinued.

4

You could generate the SQL based on what values are currently in the library/file ("table") that contains the data.

Or you could create a separate that lists the main DAMDLNs you want to include a specific line for and the rest will be "Other".