How to add Multiple column value?

Question

Picture is of table in the database

I have to query drivers who have covered more than 430,000 miles between January 2010 to December 2012. For which i am trying

SELECT t.DRIVERID 
FROM BBI_BC.TRUCKS t
WHERE t.JAN_2010_MILES + t. FEB_2010_MILES + .....+ t.DEC_2012_MILES > 43000;

Answer 1

So the question is, how to avoid typing all those columns in the WHERE clause. The answer is you can't. The designer of that table opted to ignore the well-established industry standard rules of database normalization. Find out more. Why they did that is irrelevant: the upshot is that everybody who queries the table has to write tedious and hard-to-maintain queries.

There is the option of dynamic query generation: run this query, then cut'n'paste the output into your WHERE clause:

select ' + t.' || column_name
from all_tab_columns
where owner = 'BBI_BC'
and table_name = 'TRUCKS'
and (column_name like '%2010_MILES'
     or column_name like '%2011_MILES'
     or column_name like '%2012_MILES')
order by column_id;

---

I suspect this is a homework question. It is important that you realise that the true lesson is: data modelling is very important and denormalisation is Teh Suck!

Even the way the table's columns have been named is poor: it would be better if they were named MILES_2010_01, MILES_2010_02 etc, because at least then you could have written the dynamic query as

where column_name between 'MILES_2010_01' and 'MILES_2012_12'

---

With a properly modelled table - that is, with records for each month and just two columns for MONTHLY_MILES and MONTHLY_GAS - the query would look like this:

SELECT t.DRIVERID 
FROM BBI_BC.TRUCKS t
WHERE t.year between 2010 and 2012
group by t.driverid having sum(t.monthly_miles) >43000

Answer 2

This is a similar solution to APC's , but returns the result dynamically. ( works for 12c +)

DECLARE
    miles_cond   VARCHAR2(20) := ' > 43000';
    v_where      VARCHAR2(1000);
    x            SYS_REFCURSOR;
BEGIN
    SELECT
        ' WHERE '
        ||
            LISTAGG(column_name,' + ') WITHIN GROUP(
                ORDER BY
                    column_name
            )
        || miles_cond
    INTO v_where
    FROM
        all_tab_columns
    WHERE
        owner = 'BBI_BC'
        AND table_name = 'TRUCKS'
        AND REGEXP_LIKE ( column_name,
                          '201[0-2]_MILES' )
    ORDER BY
        column_id;

    OPEN x FOR 'SELECT * FROM TRUCKS' || v_where;

    dbms_sql.return_result(x);
END;
/

And of course, you could add a similar logic like MT0's to handle nulls.

Answer 3

If the values can contain NULL values then you will need to use COALESCE or NVL:

SELECT DRIVERID 
FROM   BBI_BC.TRUCKS
WHERE  COALESCE( JAN_2010_MILES, 0)
     + COALESCE( FEB_2010_MILES, 0)
     + .....
     + COALESCE( DEC_2012_MILES, 0) > 430000;

If you want to automatically generate the query then:

SELECT 'SELECT DRIVERID 
FROM   BBI_BC.TRUCKS
WHERE  ' || LISTAGG( 'COALESCE( ' || TO_CHAR( ADD_MONTHS( DATE '2010-01-01', LEVEL - 1 ), 'MON_YYYY' ) || '_MILES, 0)', '
     + ' ) WITHIN GROUP ( ORDER BY LEVEL ) || ' > 430000;' AS sql
FROM   DUAL
CONNECT BY ADD_MONTHS( DATE '2010-01-01', LEVEL - 1 ) <= DATE '2012-12-01';

Which outputs:

SELECT DRIVERID 
FROM   BBI_BC.TRUCKS
WHERE  COALESCE( JAN_2010_MILES, 0)
     + COALESCE( FEB_2010_MILES, 0)
     + COALESCE( MAR_2010_MILES, 0)
     + COALESCE( APR_2010_MILES, 0)
     + COALESCE( MAY_2010_MILES, 0)
     + COALESCE( JUN_2010_MILES, 0)
     + COALESCE( JUL_2010_MILES, 0)
     + COALESCE( AUG_2010_MILES, 0)
     + COALESCE( SEP_2010_MILES, 0)
     + COALESCE( OCT_2010_MILES, 0)
     + COALESCE( NOV_2010_MILES, 0)
     + COALESCE( DEC_2010_MILES, 0)
     + COALESCE( JAN_2011_MILES, 0)
     + COALESCE( FEB_2011_MILES, 0)
     + COALESCE( MAR_2011_MILES, 0)
     + COALESCE( APR_2011_MILES, 0)
     + COALESCE( MAY_2011_MILES, 0)
     + COALESCE( JUN_2011_MILES, 0)
     + COALESCE( JUL_2011_MILES, 0)
     + COALESCE( AUG_2011_MILES, 0)
     + COALESCE( SEP_2011_MILES, 0)
     + COALESCE( OCT_2011_MILES, 0)
     + COALESCE( NOV_2011_MILES, 0)
     + COALESCE( DEC_2011_MILES, 0)
     + COALESCE( JAN_2012_MILES, 0)
     + COALESCE( FEB_2012_MILES, 0)
     + COALESCE( MAR_2012_MILES, 0)
     + COALESCE( APR_2012_MILES, 0)
     + COALESCE( MAY_2012_MILES, 0)
     + COALESCE( JUN_2012_MILES, 0)
     + COALESCE( JUL_2012_MILES, 0)
     + COALESCE( AUG_2012_MILES, 0)
     + COALESCE( SEP_2012_MILES, 0)
     + COALESCE( OCT_2012_MILES, 0)
     + COALESCE( NOV_2012_MILES, 0)
     + COALESCE( DEC_2012_MILES, 0) > 430000;