SQL PHP如果第二列没有值，则将一列设置为零

Question

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

Answer 1

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');

SqlFiddleDemo

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