API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 22nd, 2017
468
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 7.66 KB | None | 0 0
raw download clone embed print report
  1. state_demo2=# select * from subtest_registration where sid='635340410';
  2.                   id                  |    sid    |              subtest_id              |        create_date         |       modified_date        
  3. --------------------------------------+-----------+--------------------------------------+----------------------------+----------------------------
  4.  45a0c920-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e696b230-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.02386  | 2011-02-15 13:17:47.02386
  5.  45a16984-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6982d22-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.027687 | 2011-02-15 13:17:47.027687
  6.  45a1c42e-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6994f9a-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.029989 | 2011-02-15 13:17:47.029989
  7.  45a21c6c-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e69a7f8c-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.032258 | 2011-02-15 13:17:47.032258
  8.  45a27eb4-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e69b9674-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.034803 | 2011-02-15 13:17:47.034803
  9.  45a2e016-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e69cb7b6-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.037245 | 2011-02-15 13:17:47.037245
  10.  45a344ca-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e69dbdaa-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.039853 | 2011-02-15 13:17:47.039853
  11.  45a3a9c4-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e69ef03a-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.042446 | 2011-02-15 13:17:47.042446
  12.  45a403d8-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6a02c8e-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.044716 | 2011-02-15 13:17:47.044716
  13.  45a46472-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6a129cc-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.04769  | 2011-02-15 13:17:47.04769
  14.  45a4d25e-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6a23a38-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.050039 | 2011-02-15 13:17:47.050039
  15.  45a53230-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6a35a58-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.052472 | 2011-02-15 13:17:47.052472
  16.  45a58fa0-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6a48680-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.054859 | 2011-02-15 13:17:47.054859
  17.  45a5f134-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6a5bf8c-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.05736  | 2011-02-15 13:17:47.05736
  18.  45a65070-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6a6e268-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.059806 | 2011-02-15 13:17:47.059806
  19.  45a6b326-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6a7e8ca-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.062346 | 2011-02-15 13:17:47.062346
  20.  45a70e7a-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6a91de4-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.064709 | 2011-02-15 13:17:47.064709
  21.  45a76cee-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6aa3b02-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.067085 | 2011-02-15 13:17:47.067085
  22.  45a7c9a0-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6ab4f92-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.069467 | 2011-02-15 13:17:47.069467
  23.  45a8294a-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6ac5acc-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.07191  | 2011-02-15 13:17:47.07191
  24.  45a885ac-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6ad6318-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.074263 | 2011-02-15 13:17:47.074263
  25.  45a8dd5e-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6ae8cfc-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.077227 | 2011-02-15 13:17:47.077227
  26.  45a95b44-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6af8ff8-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.079699 | 2011-02-15 13:17:47.079699
  27.  45a9b4e0-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6b09952-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.082032 | 2011-02-15 13:17:47.082032
  28.  45aa1034-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6b84e72-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.0844   | 2011-02-15 13:17:47.0844
  29.  45aa6dd6-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6b97e82-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.086802 | 2011-02-15 13:17:47.086802
  30.  45aadcc6-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6ba7e68-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.089612 | 2011-02-15 13:17:47.089612
  31.  45ab3c3e-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6bba6bc-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.092028 | 2011-02-15 13:17:47.092028
  32.  45ab9508-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6bcd6cc-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.094316 | 2011-02-15 13:17:47.094316
  33.  45abf35e-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6bde206-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.096736 | 2011-02-15 13:17:47.096736
  34.  45ac4b1a-3938-11e0-a84e-ff2c4691c4a2 | 635340410 | e6bf0ac8-10e0-11df-80f4-1a7fbecf21f7 | 2011-02-15 13:17:47.099908 | 2011-02-15 13:17:47.099908
  35. (31 rows)
  36.  
  37.  
  38. Taking one of those:
  39.  
  40.  
  41. state_demo2=# select * from subtest_registration_result where subtest_registration_id ='45a0c920-3938-11e0-a84e-ff2c4691c4a2';
  42.        subtest_registration_id        | score_type_id | score | score_description
  43. --------------------------------------+---------------+-------+-------------------
  44.  45a0c920-3938-11e0-a84e-ff2c4691c4a2 |             1 |     1 |
  45.  45a0c920-3938-11e0-a84e-ff2c4691c4a2 |             2 |       | W
  46. (2 rows)
  47.  
  48.  
  49. Running that WITH() query:
  50.  
  51. WITH totals AS
  52.  (
  53.   SELECT score_description,
  54.   COUNT( sid) AS student_count,
  55.   ( SELECT COUNT(  sid) FROM subtest_registration )  AS total_students
  56.   FROM subtest_registration_result
  57.   INNER JOIN subtest_registration ON subtest_registration_id=id
  58.   WHERE score_type_id = 2
  59.    
  60.    GROUP BY score_description, score
  61.  )
  62.  
  63. SELECT score_description,  student_count,
  64. total_students,
  65. ( student_count * 100.00 ) /  total_students as percentage
  66.  FROM totals
  67.  
  68.  
  69. bsd:state afra$ time postgres state_demo2 < g1.sql
  70.  score_description | student_count | total_students |     percentage      
  71. -------------------+---------------+----------------+---------------------
  72.  M                 |          7803 |          40321 | 19.3521986061853625
  73.  B                 |          7996 |          40321 | 19.8308573696088887
  74.  E                 |         14596 |          40321 | 36.1994990203615982
  75.  W                 |          9926 |          40321 | 24.6174450038441507
  76. (4 rows)
  77.  
  78. This seems correct.
  79.  
  80. But 40321 students did not take test. 40321 is the number of entries in subtest_registration but a student can have multiple entries (one for each subtest).
  81.  
  82. state_demo2=# select count(distinct sid) from subtest_registration;
  83.  count
  84. -------
  85.   1193
  86. (1 row)
  87.  
  88. Do I do:
  89.  
  90. COUNT(DISTINCT sid) AS student_count,
  91.  
  92. But then the values are whacked:
  93.  
  94. WITH totals AS
  95.  (
  96.   SELECT score_description,
  97.   COUNT( distinct sid) AS student_count,
  98.   ( SELECT COUNT( distinct sid) FROM subtest_registration )  AS total_students
  99.   FROM subtest_registration_result
  100.   INNER JOIN subtest_registration ON subtest_registration_id=id
  101.   WHERE score_type_id = 2
  102.    
  103.    GROUP BY score_description, score
  104.  )
  105.  
  106. SELECT score_description,  student_count,
  107. total_students,
  108. ( student_count * 100.00 ) /  total_students as percentage
  109.  FROM totals
  110.  
  111. bsd:state afra$ time postgres state_demo2 < g1.sql
  112.  score_description | student_count | total_students |      percentage      
  113. -------------------+---------------+----------------+----------------------
  114.  B                 |          1193 |           1193 | 100.0000000000000000
  115.  E                 |          1142 |           1193 |  95.7250628667225482
  116.  M                 |          1163 |           1193 |  97.4853310980720872
  117.  W                 |          1091 |           1193 |  91.4501257334450964
  118. (4 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!