I am getting data from Sql using PHP, 2 columns, Amt and Period
usually the amount is clean that means amt and period are both available. Period is 1-12 and the amt is available for these columns
Amount Period
100 P1
125 P1
150 P2
160 P3
120 P4
130 P5
160 P6
170 P7
180 P8
145 P9
144 P10
155 P11
160 P12
I am running a query to get the amt field and period field from two different tables based on date, etc as shown below
SELECT Sum(CONVERT(FLOAT, amt)) AS sum_amt,
CONVERT(FLOAT, Substring(f.period, 2, 4)) AS period
FROM bookings b,
fiscalcal f
WHERE b.[invoice date] >= f.start
AND b.[invoice date] <= f.[end]
AND f.[year] = '2015'
GROUP BY CONVERT(FLOAT, Substring(f.period, 2, 4))
to get the required data which is correct
I am in a situation, where some periods are missing like P4, P5 is missing, so there is no row with period P4, P5, but on the webpage, I am supposed to display zero for amount in case of Period P4, P5. I am not able to do this.
so it should look like
amount period
225 P1
150 P2
160 P3
0 P4
0 P5
160 P6
170 P7
180 P8
145 P9
144 P10
155 P11
165 P12
Please help how to do this on PHP and SQL. I would appreciate greatly
CREATE TABLE YourTable
([Amount] int, [Period] varchar(3));
INSERT INTO YourTable
([Amount], [Period])
VALUES
(100, 'P1'), (125, 'P1'),
(150, 'P2'),
(160, 'P3'), (120, 'P3'),
(130, 'P6'), (160, 'P6'),
(170, 'P7'),
(180, 'P8'),
(145, 'P9'),
(144, 'P10'),
(155, 'P11'),
(160, 'P12');
WITH periods (period_id) AS (
SELECT 'P1' UNION ALL
SELECT 'P2' UNION ALL
SELECT 'P3' UNION ALL
SELECT 'P4' UNION ALL
SELECT 'P5' UNION ALL
SELECT 'P6' UNION ALL
SELECT 'P7' UNION ALL
SELECT 'P8' UNION ALL
SELECT 'P9' UNION ALL
SELECT 'P10' UNION ALL
SELECT 'P11' UNION ALL
SELECT 'P12'
)
SELECT SUM(COALESCE(Amount, 0)), p.period_id
FROM periods p
LEFT JOIN YourTable y
ON p.period_id = y.Period
GROUP BY p.period_id
OUTPUT
| | period_id |
|-----|-----------|
| 225 | P1 |
| 150 | P2 |
| 280 | P3 |
| 0 | P4 |
| 0 | P5 |
| 290 | P6 |
| 170 | P7 |
| 180 | P8 |
| 145 | P9 |
| 144 | P10 |
| 155 | P11 |
| 160 | P12 |
EDIT
After your edit looks like you can just put your query inside another cte.
WITH periods (period_id) AS (
SELECT 'P1' UNION ALL
SELECT 'P2' UNION ALL
SELECT 'P3' UNION ALL
SELECT 'P4' UNION ALL
SELECT 'P5' UNION ALL
SELECT 'P6' UNION ALL
SELECT 'P7' UNION ALL
SELECT 'P8' UNION ALL
SELECT 'P9' UNION ALL
SELECT 'P10' UNION ALL
SELECT 'P11' UNION ALL
SELECT 'P12'
),
YourTable (Amount, period) AS (
SELECT Sum(CONVERT(FLOAT, amt)) AS sum_amt,
CONVERT(FLOAT, Substring(f.period, 2, 4)) AS period
FROM bookings b,
fiscalcal f
WHERE b.[invoice date] >= f.start
AND b.[invoice date] <= f.[end]
AND f.[year] = '2015'
GROUP BY CONVERT(FLOAT, Substring(f.period, 2, 4))
)
SELECT SUM(COALESCE(Amount, 0)), p.period_id
FROM periods p
LEFT JOIN YourTable y
ON p.period_id = y.Period
GROUP BY p.period_id
Table of valid Periods with a field call Period (including P01 - P12).
SELECT periods.period, sum(convert(float,AMT)) as sum_amt2
from Periods
left join Sales on periods.period = substring(sales.period,2,4)
group by periods.period