I am really bad at SQL

[NJDevils28]

Hello all,

I need to omit or include data for my counts using a where clause and I'm struggling to figure it out.
My table information is as follows.

Table Name = Volume_Information
Table Field Name (To Count) = VI_Creation_Date
Table Field Name to base counts on = VI_Q_Key

The code below will generate the Category Name and the corresponding counts. That's fine and not giving me any grief.
I need to generate a similar report only I need to filter out (Or just include) certain values from a Table Field.

VI_Q_Key is a text field. I need to count only records that have a Field value of "1" or "2" or "7" or "9" (in this case it's all numerics). I know I need to add a Where clause, but after experimenting with the code below, it fails every time, no matter how I format the Where Clause.

I will also have to add a Parameter to the SQL Statement, to allow a user to drive the include/exclude logic but I will address that later.

SELECT cat, COUNT(*) AS qty
FROM(SELECT days, CASE 
WHEN C.days < 1 THEN 'Under 1 Day' 
WHEN C.days < 7 THEN 'Under 1 Week' 
WHEN C.days < 31 THEN 'Under 1 Month' 
WHEN C.days < 366 THEN 'Under 1 Year' 
WHEN C.days < 731 THEN 'Under 2 Years'
WHEN C.days < 1826 THEN 'Under 5 Years' 
WHEN C.days < 3651 THEN 'Under 10 Years' 
ELSE 'Over 10 Years' END AS cat
FROM(SELECT julianday('now') - julianday([REPLACE](substr(VI_Creation_Date, 1, 10), '/', '-')) AS days
FROM  Volume_Information) C) G
GROUP BY cat
ORDER BY cat

Any help is greatly appreciated!

Thanks,

-NJ

[digitalShaman]

i'd put it here:

SELECT cat, COUNT(*) AS qty
FROM(SELECT days, CASE 
WHEN C.days < 1 THEN 'Under 1 Day' 
WHEN C.days < 7 THEN 'Under 1 Week' 
WHEN C.days < 31 THEN 'Under 1 Month' 
WHEN C.days < 366 THEN 'Under 1 Year' 
WHEN C.days < 731 THEN 'Under 2 Years'
WHEN C.days < 1826 THEN 'Under 5 Years' 
WHEN C.days < 3651 THEN 'Under 10 Years' 
ELSE 'Over 10 Years' END AS cat
FROM(SELECT julianday('now') - julianday([REPLACE](substr(VI_Creation_Date, 1, 10), '/', '-')) AS days
FROM  Volume_Information) C) G
WHERE VI_Q_Key IN (1,2,7,9)
GROUP BY cat
ORDER BY cat

you can also use Parameters for this but be Aware that you cannot pass a list for a IN() clause as Parameter. there was a thread here recently that also Shows some Workarounds.

PS: you still got that string replace with the date?!

PPS: no wait a Minute... what is This select(select(select ? my answer to place the "where" might not work this way. i Need to understand your query first...

[digitalShaman]

indentation often helps

SELECT cat, COUNT(*) AS qty
FROM(SELECT days, CASE 
        WHEN C.days < 1 THEN 'Under 1 Day' 
        WHEN C.days < 7 THEN 'Under 1 Week' 
        WHEN C.days < 31 THEN 'Under 1 Month' 
        WHEN C.days < 366 THEN 'Under 1 Year' 
        WHEN C.days < 731 THEN 'Under 2 Years'
        WHEN C.days < 1826 THEN 'Under 5 Years' 
        WHEN C.days < 3651 THEN 'Under 10 Years' 
        ELSE 'Over 10 Years' END AS cat
    FROM(SELECT julianday('now') - julianday([REPLACE](substr(VI_Creation_Date, 1, 10), '/', '-')) AS days
         FROM  Volume_Information
         WHERE VI_Q_Key IN (1,2,7,9)
        ) C
    ) G
GROUP BY cat
ORDER BY cat

[NJDevils28]

indentation often helps

SELECT cat, COUNT(*) AS qty
FROM(SELECT days, CASE 
        WHEN C.days < 1 THEN 'Under 1 Day' 
        WHEN C.days < 7 THEN 'Under 1 Week' 
        WHEN C.days < 31 THEN 'Under 1 Month' 
        WHEN C.days < 366 THEN 'Under 1 Year' 
        WHEN C.days < 731 THEN 'Under 2 Years'
        WHEN C.days < 1826 THEN 'Under 5 Years' 
        WHEN C.days < 3651 THEN 'Under 10 Years' 
        ELSE 'Over 10 Years' END AS cat
    FROM(SELECT julianday('now') - julianday([REPLACE](substr(VI_Creation_Date, 1, 10), '/', '-')) AS days
         FROM  Volume_Information
         WHERE VI_Q_Key IN (1,2,7,9)
        ) C
    ) G
GROUP BY cat
ORDER BY cat

Ah. I was all over the frigin place. That actually makes sense adding the "Where" after the Table Name. I will run the job and report back!

Thanks!

-NJ