How to Make Two or More Select Categorized in the Same Query

Question

How to Make Two or More Select Categorized in the Same Query

Navigation

#1 by (0 votes)

1

I need to get all the words with 'A' and play in 'WordsComA' and all with 'B' and play WordsComB, and can have C, D AND ... The closest until now was this, but funcioana only with the first.

SELECT
    w1.wd as PalavrasComA,
    w2.wd as PalavrasComB
From ptbr as w1, ptbr as w2 

where left (w1.wd, 1) = 'a'

and left (w2.wd, 1) = 'b';

mysql select

asked by anonymous 22.09.2017 / 21:50

1 answer

FPDF - I need to customize the text that will be printed How do I end this function by clicking the end button?

score 0 · Answer 1

If you want to get all of them in the same fixed query you can do as in the example:

SELECT COUNT(CASE WHEN left(w.wd, 1) = 'a' THEN 1 END) AS a,
       COUNT(CASE WHEN left(w.wd, 1) = 'b' THEN 1 END) AS b,
       COUNT(CASE WHEN left(w.wd, 1) = 'c' THEN 1 END) AS c
  FROM ptbr w

If you want to return the words:

SELECT left(w.wd, 1) AS letra,
       GROUP_CONCAT(w.wd SEPARATOR ',') AS palavras,
       COUNT(CASE WHEN left(w.wd, 1) = 'c' THEN 1 END) AS c
  FROM ptbr w
 GROUP BY left(w.wd, 1)

If you want to separate by columns:

SELECT GROUP_CONCAT(IF(left(w.wd, 1) = 'a', w.wd ,'') SEPARATOR ', ') AS palavrasComA,
       GROUP_CONCAT(IF(left(w.wd, 1) = 'b', w.wd ,'') SEPARATOR ', ') AS palavrasComB,
       GROUP_CONCAT(IF(left(w.wd, 1) = 'c', w.wd ,'') SEPARATOR ', ') AS palavrasComC
  FROM ptbr w

Or if you just want the first word:

SELECT MIN(IF(left(w.wd, 1) = 'a', w.wd, NULL)) AS palavrasComA,
       MIN(IF(left(w.wd, 1) = 'b', w.wd, NULL)) AS palavrasComB,
       MIN(IF(left(w.wd, 1) = 'c', w.wd, NULL)) AS palavrasComC,
  FROM ptbr w