Rheine Rheine - 6 months ago 8
SQL Question

Inserting complex count() query results to table


  • Note: This question was previously oversimplified; the question has been appended to give a better understanding of my problem



I have table of shipment data of a company, called t_shipment. (Some of) the headers are acc_num, type_of_business, contract_exception, payment_status, etc.

I need to make a recap table for the another dept.'s side of work. So I make a new table, ship_recap, using CREATE TABLE.

CREATE TABLE ship_recap
(vol_lumber(int), vol_oil(int);)


Then I need to recap the relevant data from t_shipment to the ship_recap. I used

INSERT INTO ship_recap (vol_lumber)
SELECT COUNT(acc_num) from t_shipment WHERE type_of_business = 'LMB' and (contract_exception = 'VALID' OR payment_status IS NOT NULL)
INSERT INTO ship_recap (vol_oil)
SELECT COUNT(acc_num) from t_shipment where type_of_business = 'OIL' and (contract_exception = 'VALID' OR payment_status IS NOT NULL);)


It ran, but the result was:

____________________
|vol_lumber| vol_oil |
----------------------
| 150 | NULL |
| NULL | 230 |
----------------------


But instead I want them to be:

____________________
|vol_lumber| vol_oil |
----------------------
| 150 | 230 |
----------------------


I tried using

INSERT INTO ship_recap (vol_lumber, vol_oil)
(SELECT COUNT(acc_num) from t_shipment WHERE type_of_business = 'LMB' and (contract_exception = 'VALID' OR payment_status IS NOT NULL),
SELECT COUNT(acc_num) from t_shipment where type_of_business = 'OIL' and (contract_exception = 'VALID' OR payment_status IS NOT NULL);)


And permutations of the same logic (e.g. changing the comma into a semicolon, or taking out the parentheses), but it returned a syntax error every time.

The results/recap table might have as many as 20+ headers and other queries might get slightly more complicated too.
I need a way to correctly insert the SELECT/COUNT-ed data to the recap table and keep them in one row. Thank you beforehand.

EDIT: As per Reno's suggestion, I tried this

CREATE TABLE ship_recap (vol_OIL int,vol_LUM int,vol_BEV int,processed_OIL int,processed_LUM int,processed_BEV int);
INSERT INTO ship_recap (vol_OIL, vol_LUM, vol_BEV, processed_OIL, processed_LUM, processed_BEV)
SELECT
COUNT(IF(type_of_business = 'OIL' and process_date = '2016-05-12', 1, NULL),
COUNT(IF(type_of_business = 'LUM' and process_date = '2016-05-12', 1, NULL),
COUNT(IF(type_of_business = 'BEV' and process_date = '2016-05-12', 1, NULL),
COUNT(IF(type_of_business = 'OIL' and process_date = '2016-05-12' and (contract_exception = ‘VALID’ OR payment_status IS NOT NULL), 1, NULL)),
COUNT(IF(type_of_business = 'LUM' and process_date = '2016-05-12' and (contract_exception = ‘VALID’ OR payment_status IS NOT NULL), 1, NULL)),
COUNT(IF(type_of_business = 'BEV' and process_date = '2016-05-12' and (contract_exception = ‘VALID’ OR payment_status IS NOT NULL), 1, NULL)) FROM t_shipment;


It still returned a syntax error.

Answer

Try following sql, may help for you;)

 CREATE TABLE ship_recap (vol_OIL int,vol_LUM int,vol_BEV int,processed_OIL int,processed_LUM int,processed_BEV int);
 INSERT INTO ship_recap (vol_OIL, vol_LUM, vol_BEV, processed_OIL, processed_LUM, processed_BEV)
 SELECT
  COUNT(IF(type_of_business = 'OIL' and process_date = '2016-05-12', 1, NULL)),
  COUNT(IF(type_of_business = 'LUM' and process_date = '2016-05-12', 1, NULL)),
  COUNT(IF(type_of_business = 'BEV' and process_date = '2016-05-12', 1, NULL)),
  COUNT(IF(type_of_business = 'OIL' and process_date = '2016-05-12' and (contract_exception = ‘VALID’ OR payment_status IS NOT NULL), 1, NULL)),
  COUNT(IF(type_of_business = 'LUM' and process_date = '2016-05-12' and (contract_exception = ‘VALID’ OR payment_status IS NOT NULL), 1, NULL)),
  COUNT(IF(type_of_business = 'BEV' and process_date = '2016-05-12' and (contract_exception = ‘VALID’ OR payment_status IS NOT NULL), 1, NULL)) FROM t_shipment;