SQL counting
-
I want to get a count of an ID where the ID is either = -999 or its not. So i have 2 counts in total. I used union all to get that but i think there would be better way to get the count from the same field. any help would be appreciated. select count(ID) as Id_999 from table_A where ID='-999' union all select count(ID) from table_A where ID<>'-999' there is no null values allowed so it either a number or -999 if null when table is loaded.
-
I want to get a count of an ID where the ID is either = -999 or its not. So i have 2 counts in total. I used union all to get that but i think there would be better way to get the count from the same field. any help would be appreciated. select count(ID) as Id_999 from table_A where ID='-999' union all select count(ID) from table_A where ID<>'-999' there is no null values allowed so it either a number or -999 if null when table is loaded.
-
You could turn the counts into separate columns: select SUM ( CASE WHEN ID = '-999' THEN 1 ELSE 0 END) AS ID999_count, SUM ( CASE WHEN ID <> '-999' THEN 1 ELSE 0 END) AS nonID999_count from table_A Scott
-
I want to get a count of an ID where the ID is either = -999 or its not. So i have 2 counts in total. I used union all to get that but i think there would be better way to get the count from the same field. any help would be appreciated. select count(ID) as Id_999 from table_A where ID='-999' union all select count(ID) from table_A where ID<>'-999' there is no null values allowed so it either a number or -999 if null when table is loaded.
Qazzy64 wrote:
there is no null values allowed so it either a number or -999 if null when table is loaded.
Just curious; does that mean that you replace a null-test with a test for -999, and act according? Why was null disallowed anyway?
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
Qazzy64 wrote:
there is no null values allowed so it either a number or -999 if null when table is loaded.
Just curious; does that mean that you replace a null-test with a test for -999, and act according? Why was null disallowed anyway?
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
When they load the table it has a number(ID) or if its null they load -999 so they know to reprocess it later on when other matching data shows up. Hope that helps.
The reason I asked is because it does not make any sense to me; you'd still have to deal with the same type of checks. The only difference being that the new "strategy" will be alien to new developers, and will cause weirder and harder to debug-exceptions than a null-reference would. I might be missing some obvious advantage of the approach. So, where is it? What's the 'added value' of using a marker-value above a null-value?
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
The reason I asked is because it does not make any sense to me; you'd still have to deal with the same type of checks. The only difference being that the new "strategy" will be alien to new developers, and will cause weirder and harder to debug-exceptions than a null-reference would. I might be missing some obvious advantage of the approach. So, where is it? What's the 'added value' of using a marker-value above a null-value?
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
I agree with you that doing anything causes more problems eventually. Myself i would not have brought the data forward until all fields are present from source file. But i do not make that choice and i got involved after project was started. This is more of a transactional system then data warehouse like they said. Hate to say it but it goes back to the way it was architected and going forward from there has been to get it to work. Very complex data modeling which i would have simplified by bringing it in and leveling data types. They chose to bring it forward anyway and then leave just before the DW level. Makes no sense but i wasnt in on those meetings. I get to deal with it and figure out if the data is accurate. Fun times. :)