SQLey blocks

[bobbingwide]

Write some blocks which can be used to display information in Statistically boring.

Use SQL like this to display the chart of SB's counts by length

SELECT length( post_title), count(*) FROM `wp_posts` where post_type = 'bigram' and post_status = 'publish' group by length( post_title ) 

Perhaps another to count them by second letters?

[bobbingwide]

localhost/rcorguk_wp246/wp_posts/ https://c17064.sgvps.net/phpmyadmin/index.php?route=/sql&db=rcorguk_wp246&table=wp_posts&pos=0

Showing rows 0 - 22 (23 total, Query took 0.0076 seconds.)

SELECT length( post_title), count(*) FROM wp_posts where post_type = 'bigram' and post_status = 'publish' group by length( post_title );

length( post_title) count(*)
3 8
5 6
6 9
7 37
8 108 9 243 10 387 11 492 12 459 13 506 14 404 15 329 16 224 17 156 18 115 19 72
20 48
21 18
22 12
23 8
24 5
25 1
55 1

[bobbingwide]

Count of B-words

SELECT length(meta_value), meta_value, count(*) 
FROM `wp_postmeta` 
where meta_key = 'B-word' 
group by length( meta_value );

localhost/rcorguk_wp246/wp_postmeta/ https://c17064.sgvps.net/phpmyadmin/index.php?route=/sql&db=rcorguk_wp246&table=wp_postmeta&pos=0

Showing rows 0 - 12 (13 total, Query took 0.0035 seconds.)

SELECT length(meta_value), count(*) FROM wp_postmeta where meta_key = 'B-word' group by length( meta_value );

length(meta_value) count(*)
1 7
2 16
3 95
4 217 5 224 6 186 7 162 8 87
9 61
10 16
11 12
12 5
20 1

Count of S-words

SELECT length(meta_value), meta_value, count(*) 
FROM `wp_postmeta` 
where meta_key = 'S-word' group by length( meta_value );

Aha! There are 3648 bigrams but only 1089 S-Words and B-words that leaves 2559 posts which don't have S-words or B-words.

These are Sampled Bigrams where the S-word and B-word have been saved as taxonomy terms and don't have post meta values for the same things.

So, we can't use the above SQL.

We have to use something more complicated.