search

DalgoT4D / dbt_lahi

0 stars 2 forks source link

Aggregation of data across all the states - LAHI (Catalyst Dashboard) #16

Closed siddhant3030 closed 11 months ago

siddhant3030 commented 12 months ago

Image

  1. Vocational education schools
  2. Vocational Trainer Program (VTPs)
  3. Vocation Trainer (VT)
  4. Sectors
  5. Job roles (9th to 10th)
  6. Job roles (11th to 12th)
  7. Total Boys
  8. Total Girls
  9. Grand Total (Boys + Girls)
Abhishek-N commented 11 months ago

  1. Vocational education schools: looks good

  2. VTP: NA, No information available, Not available

  3. VT: No information available, NULL, NA

  4. Sectors: NULL

  5. Job roles (9th to 10th): No information available, NA, NULL

  6. Job roles (11th to 12th): NULL, NA

  7. Total Boys: NULL

  8. Total Girls: NULL

  9. Grand Total (Boys + Girls): NULL

siddhant3030 commented 11 months ago

Something like this will help you get rows with only valid values

SELECT * FROM union_state WHERE column1 NOT IN ('NA', 'No information available', 'Not available') AND column2 NOT IN ('NA', 'No information available', 'Null') AND column3 NOT IN ('NA', 'No information available', 'NA');

Abhishek-N commented 11 months ago
  1. VE schools - 5937
  2. VTPs - 68
  3. VT - 3487
  4. Sectors - 35
  5. Job roles (9th to 10th) - 86
  6. Job roles (11th to 12th) - 49
  7. Total boys - 258
  8. Total girls - 279
  9. Grand total - 333
siddhant3030 commented 11 months ago

So we need a grand total of each state. basically sum the rows to get this

Abhishek-N commented 11 months ago

Grand total: Andhra Pradesh - 57127 Assam - 74831 Chhattisgarh - 116225 Delhi - 125118 Gujarat - 42903 Himachal Pradesh - 77932 Karnataka - 22092 Maharashtra - 68229 Uttarakhand - 37052

Total boys: Andhra Pradesh - 23727 Assam - 36092 Chhattisgarh - 50334 Delhi - 52230 Gujarat - 19423 Himachal Pradesh - 0 Karnataka - 10986 Maharashtra - 31286 Uttarakhand - 17356

Total Girls: Andhra Pradesh - 33400 Assam - 38739 Chhattisgarh - 65891 Delhi - 72888 Gujarat - 23541 Himachal Pradesh - 0 Karnataka - 11106 Maharashtra - 36943 Uttarakhand - 19696

siddhant3030 commented 11 months ago

@Abhishek-N can you also add

  1. job_roles calculation for both 9th-10th and 11th-12th
  2. school name calculation

We will try to compare it and fix it if something is wrong

Abhishek-N commented 11 months ago
  1. job_roles calculation for both 9th-10th and 11th-12th = 86 + 61 = 147
  2. school name calculation = 5937
siddhant3030 commented 11 months ago

@Abhishek-N hey. We need to mention job roles separately for each one basically for 9th and 10th then 11th and 12th

Abhishek-N commented 11 months ago

yes 86 and 61 for 9th to 10th and 11th to 12th resp.