I am generating data for a jsdatatables display - there are four sites and I need to pull the data into columns for each. Most columns will only hold a number, but sometimes there are notes as well and I need to add these to the columns.
my currently non-working example is here
SELECT a.drug as drug,
CONCAT(SUM(IF(a.idSite=1, IF(a.expiry > now(),quantity,0), 0)),' ',notes) AS 'col1',
SUM(IF(a.idSite=2, IF(a.expiry > now(),quantity,0), 0)) AS 'col2',
SUM(IF(a.idSite=3, IF(a.expiry > now(),quantity,0), 0)) AS 'col3',
SUM(IF(a.idSite=4, IF(a.expiry > now(),quantity,0), 0)) AS 'col4',
1,2
FROM items a left join sites s on a.idSite = s.id
WHERE a.deleted_at IS NULL
Group By drug, notes
I've deliberately left the other three columns as number only - and the data currently contains the identical data for col1 & col3
which gives me
+----------------------------------------------------------------------------------------------------------------------------------+-------------------+------+------+------+---+---+
| drug | col1 | col2 | col3 | col4 | 1 | 2 |
+----------------------------------------------------------------------------------------------------------------------------------+-------------------+------+------+------+---+---+
| (c) Absolute Ethyl Alcohol 98% i.v. Inj 1 ml | 0 n/a | 0 | 0 | 0 | 1 | 2 |
| (c) Diazepam 10mg/2ml Injection | 1 to be requested | 0 | 1 | 0 | 1 | 2 |
| (r) Fomepizole 1 g/ml (1.5ml) i.v. Solution (preservative Free) | 8 n/a | 0 | 8 | 0 | 1 | 2 |
| Acetylcysteine 20% | NULL | 0 | 70 | 0 | 1 | 2 |
| Activated Charcoal | NULL | 0 | 30 | 0 | 1 | 2 |
| Atropine 0.1mg/ml Pre Loaded Syringe | NULL | 0 | 100 | 0 | 1 | 2 |
| Calcium Disodium Edetate 200mg/ml | NULL | 0 | 0 | 0 | 1 | 2 |
| Calcium gluconate 10% | NULL | 0 | 20 | 0 | 1 | 2 |
| Cyanide Kit Sodium Nitrite 300mg/10 ml + Sodium Thiosulfate 12.5 g/50 ml + Amyl Nitrite 0.3 ml Or Hydroxocobalamin Hydrochloride | 0 Non Formulary | 0 | 0 | 0 | 1 | 2 |
items table
+-----+------------+----------------------------------------------------------------------------------------------------------------------------------+----------+------------+--------+----------+-----------------+
| id | deleted_at | drug | quantity | expiry | idSite | barcode | notes |
+-----+------------+----------------------------------------------------------------------------------------------------------------------------------+----------+------------+--------+----------+-----------------+
| 773 | NULL | (c) Absolute Ethyl Alcohol 98% i.v. Inj 1 ml | 0 | 2021-07-18 | 3 | 41125225 | n/a |
| 739 | NULL | (c) Absolute Ethyl Alcohol 98% i.v. Inj 1 ml | 0 | 2021-07-18 | 1 | 41125225 | n/a |
| 772 | NULL | (c) Diazepam 10mg/2ml Injection | 1 | 2020-08-01 | 3 | 41061007 | to be requested |
| 738 | NULL | (c) Diazepam 10mg/2ml Injection | 1 | 2020-08-01 | 1 | 41061007 | to be requested |
| 774 | NULL | (r) Fomepizole 1 g/ml (1.5ml) i.v. Solution (preservative Free) | 8 | 2021-07-01 | 3 | 41992542 | n/a |
| 740 | NULL | (r) Fomepizole 1 g/ml (1.5ml) i.v. Solution (preservative Free) | 8 | 2021-07-01 | 1 | 41992542 | n/a |
| 708 | NULL | Acetylcysteine 20% | 70 | 2019-11-01 | 1 | 41121031 | NULL |
| 742 | NULL | Acetylcysteine 20% | 70 | 2019-11-01 | 3 | 41121031 | NULL |
| 709 | NULL | Activated Charcoal | 30 | 2020-01-01 | 1 | 41125067 | NULL |
| 743 | NULL | Activated Charcoal | 30 | 2020-01-01 | 3 | 41125067 | NULL |
| 710 | NULL | Atropine 0.1mg/ml Pre Loaded Syringe | 100 | 2021-07-01 | 1 | 41061203 | NULL |
| 744 | NULL | Atropine 0.1mg/ml Pre Loaded Syringe | 100 | 2021-07-01 | 3 | 41061203 | NULL |
| 711 | NULL | Calcium Disodium Edetate 200mg/ml | 30 | 2019-06-01 | 1 | 41121009 | NULL |
| 745 | NULL | Calcium Disodium Edetate 200mg/ml | 30 | 2019-06-01 | 3 | 41121009 | NULL |
| 712 | NULL | Calcium gluconate 10% | 20 | 2020-11-01 | 1 | 41091003 | NULL |
| 746 | NULL | Calcium gluconate 10% | 20 | 2020-11-01 | 3 | 41091003 | NULL |
| 770 | NULL | Cyanide Kit Sodium Nitrite 300mg/10 ml + Sodium Thiosulfate 12.5 g/50 ml + Amyl Nitrite 0.3 ml Or Hydroxocobalamin Hydrochloride | 0 | 2021-07-18 | 3 | 41002215 | Non Formulary |
| 736 | NULL | Cyanide Kit Sodium Nitrite 300mg/10 ml + Sodium Thiosulfate 12.5 g/50 ml + Amyl Nitrite 0.3 ml Or Hydroxocobalamin Hydrochloride | 0 | 2021-07-18 | 1 | 41002215 | Non Formulary |
My problem is that the concat column only returns a value when there are notes to display - I'm sure I'm doing something silly
