CODEWITHSUNDEEP
mysql
delishas
delishas

Reputation: 17

How do you merge two tables and keep the column they share while adding the other columns they don't?

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.

Upvotes: 0

Views: 39

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

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

Upvotes: 1

Related Questions