Mysql count from multiple columns?

Question

I have the following simplified tables:

  statistics
 +-------------+-------------+---------------+
 | type        | itemnumber | borrowernumber |
 +-------------+-------------+---------------+
 | issue       | 26191       | 11978         |
 +-------------+-------------+---------------+
 | issue       | 26190       | 11979         |
 +-------------+-------------+---------------+


items:
 +-------------+-------------+
 | itemnumber  | bibliono    |
 +-------------+-------------+
 | 26191       | 27          |
 +-------------+-------------+
 | 26190       | 28          |
 +-------------+-------------+


biblio_metadata:
 +-------------+----------------------------------------------------+
 | bibliono    | metadata                                           |
 +-------------+----------------------------------------------------+
 | 27          | <?xml.. <datafield tag="082" ind1="0" ind2="4">    |
 |                        <subfield code="a">005.133/M29</subfield> |
 |                         </datafield>                             |                                                      
 +-------------+----------------------------------------------------+
 | 28          | <?xml.. <datafield tag="082" ind1="0" ind2="4">    |
 |                        <subfield code="a">995.133/M29</subfield> |
 |                         </datafield>                             |                                                      
 +-------------+----------------------------------------------------+

  borrowers
 +-------------+-------------+
 | borrowerno  | sort1       |
 +-------------+-------------+
 | 11978       | CAS         |
 +-------------+-------------+
 | 11979       | CBA         |
 +-------------+-------------+

I want to get the following through a mysql query:

  +-------------+------------+
  | DDC Range   | CAS | CBA |
  +-------------+------------
  | 001-100     | 1   |     |
  +-------------+------------
  | 900-999     |     | 1   |
  +-------------+-----------+

I'm trying to find the right combination of queries - if it's mysql select query multiple columns or any other keyword but can't seem to get the right term to search. I have the following made up mysql queries but can't go pass the first column 'CAS' and further query the other sort1's (in this example CBA).

SELECT CASE
WHEN ExtractValue(metadata, '//datafield[@tag="082"]/subfield[@code="a"]') REGEXP '^[0]{1}[0-9]{2}[^0-9]+.*' THEN "000-099"
WHEN ExtractValue(metadata, '//datafield[@tag="082"]/subfield[@code="a"]') REGEXP '^[9]{1}[0-9]{2}[^0-9]+.*' THEN "900-999"
ELSE "Others" 
END as "DDC Range", count(borrowers.sort1) 
from statistics s 
LEFT JOIN items on (s.itemnumber=items.itemnumber) 
LEFT JOIN biblio_metadata ON (items.biblionumber=biblio_metadata.biblionumber) 
LEFT JOIN borrowers on (s.borrowernumber=borrowers.borrowernumber) 
WHERE s.type = "issue" 
AND borrowers.sort1="CAS" 
GROUP BY Subjects

I'm looking into this COUNT(*) from multiple tables in MySQL but I don't know where to put the next query or if what I'm trying to arrive at is related to the aforementioned link. Thanks in advance

LeroyFromBerlin · Accepted Answer · 2020-01-22 16:15:26Z

1

Looks like in your query you're filtering out all entries where borrowers.sort1="CAS" but if I understand correctly you'll need these.

Can't you just do all the joins as specified in your question and then use two case statements? As it seems you're interested in the count(*) per subject you can then sum them.

Maybe try something like below:

SELECT 
CASE
WHEN ExtractValue(metadata, '//datafield[@tag="082"]/subfield[@code="a"]') REGEXP '^[0]{1}[0-9]{2}[^0-9]+.*' THEN "000-099"
WHEN ExtractValue(metadata, '//datafield[@tag="082"]/subfield[@code="a"]') REGEXP '^[9]{1}[0-9]{2}[^0-9]+.*' THEN "900-999"
ELSE "Others" 
END as "DDC Range", count(borrowers.sort1),
sum(case when borrowers.sort1="CAS" then 1 else '' end) as 'CAS',
sum(case when borrowers.sort1="CBA" then 1 else '' end) as 'CBA'
from statistics s 
LEFT JOIN items on (s.itemnumber=items.itemnumber) 
LEFT JOIN biblio_metadata ON (items.biblionumber=biblio_metadata.biblionumber) 
LEFT JOIN borrowers on (s.borrowernumber=borrowers.borrowernumber) 
WHERE s.type = "issue"
GROUP BY Subjects

edited Jan 22, 2020 at 16:15

answered Jan 22, 2020 at 15:46

LeroyFromBerlin

3951 gold badge3 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

schnydszch Over a year ago

Thanks for this. It seems this is what I need, but I have to verify with the dataset first if it indeed sums. Will upvote your comment once verified. Thanks! :)

schnydszch Over a year ago

Thanks! The mysql query worked perfectly. Did not think of the sum case function combinations. Cheers!

Collectives™ on Stack Overflow

Mysql count from multiple columns?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related