0

Say I have these two tables: 

+--------+-----+ 
| bookID | Jan | 
+--------+-----+ 
| 1094   | 1   | 
| 1058   | 1   | 
| 984    |  1  | 
+--------+-----+ 
+--------+-----+ 
| bookID | Dec | 
+--------+-----+ 
| 1      | 1   | 
| 2      | 1   | 
+--------+-----+

I would like to get: 

+--------+-----+-----+ 
| bookID | Jan | Dec |
+--------+-----+-----+ 
| 1094   | 1   | 0   | 
| 1058   | 1   | 0   | 
| 984    | 1   | 0   | 
| 1      | 0   | 1   | 
| 2      | 0   | 1   | 
+--------+-----+-----+

I was thinking of using join on the id and using union to get bookID's that aren't in both tables, but it seemed inefficient because I would have to do the same for the rest of the months.

Would there be a better way to achieve this from this table: 

+----+--------+-----------+--------------+------------+------------+----------+ 
| id | bookID | studentID | checkOutDate | returnDate |  dueDate   | extended | 
+----+--------+-----------+--------------+------------+------------+----------+ 
| 1  | 25     | 1         | 2019-12-09   |  NULL      | 2019-12-25 | 0        | 
| 2  | 357    | 2         | 2019-12-09   | NULL       | 2019-12-25 | 0        | 
| 3  | 365    | 3         | 2019-12-09   | NULL       | 2019-12-25 | 0        | 
| 4  | 984    | 8         | 2019-12-09   | NULL       | 2019-12-25 | 0        |
| 5  | 1094   | 1         | 2019-12-09   | NULL       | 2019-12-25 | 0        | 
| 11 | 99     | 2         | 2019-01-11   | NULL       | 2019-12-10 | 0        | 
| 12 | 1      | 2         | 2019-01-24   | NULL       | 2019-01-11 | 0        | 
+----+--------+-----------+--------------+------------+------------+----------+

I want to display a list of books, and, for each book, 13 columns: 12 columns showing the number of checkouts for each month and a 13th column to display total year checkouts for the book.

delishas
  • 17
  • 1
  • 3
  • it's easier to show example queries if you tell us what the table names are – ysth Dec 12 '19 at 05:29
  • 1
    Does this answer your question? [How to do a FULL OUTER JOIN in MySQL?](https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql) – philipxy Feb 07 '20 at 05:58
  • 1
    Please just ask 1 question per post. The first question here is a frequent duplicate. (You describe a full outer join.) Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. – philipxy Feb 07 '20 at 06:04
  • If your issue is resolved, please accept an answer by clicking on the checkmark on the left hand side of the answer. And feel free to upvote it as well. – petezurich Mar 06 '20 at 20:48

1 Answers1

1

It's fairly straight forward (but a little verbose) to do from the original table using a CASE to get the count for each month as a column and GROUP BY to get a row by book and year.

SELECT 
  bookID, YEAR(checkoutDate) `year`,
  COUNT(CASE WHEN MONTH(checkoutDate) =  1 THEN 1 END) `jan`,
  COUNT(CASE WHEN MONTH(checkoutDate) =  2 THEN 1 END) `feb`,
  COUNT(CASE WHEN MONTH(checkoutDate) =  3 THEN 1 END) `mar`,
  COUNT(CASE WHEN MONTH(checkoutDate) =  4 THEN 1 END) `apr`,
  COUNT(CASE WHEN MONTH(checkoutDate) =  5 THEN 1 END) `may`,
  COUNT(CASE WHEN MONTH(checkoutDate) =  6 THEN 1 END) `jun`,
  COUNT(CASE WHEN MONTH(checkoutDate) =  7 THEN 1 END) `jul`,
  COUNT(CASE WHEN MONTH(checkoutDate) =  8 THEN 1 END) `aug`,
  COUNT(CASE WHEN MONTH(checkoutDate) =  9 THEN 1 END) `sep`,
  COUNT(CASE WHEN MONTH(checkoutDate) = 10 THEN 1 END) `oct`,
  COUNT(CASE WHEN MONTH(checkoutDate) = 11 THEN 1 END) `nov`,
  COUNT(CASE WHEN MONTH(checkoutDate) = 12 THEN 1 END) `dec`,
  COUNT(*) `year total checkouts`
FROM myTable
GROUP BY bookID, YEAR(checkoutDate)

An SQLfiddle to test with

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294