Select Statement returning incorrect information

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Select Statement returning incorrect information

Ron Barnes
Hello All,

With the select statement below and my test data of 43 files, I expected the following results

22 'Less than 1MB'
4 'Less than 5MB'
7 'Less than 10MB'
4 'Less than 15MB'
6 'Less than 20MB'

Instead I get
16 'Less than 1MB'
18 'Less than 5MB'
9 'Larger than 25GB'

I have been pulling my hair out trying to figure out where I went south.  If someone could, would you point out my mistake, please?


SELECT        category, COUNT(*) AS Expr1
FROM            (SELECT        (CASE
WHEN VI_File_Len < 1024000 THEN 'Less Than 1MB'
WHEN VI_File_Len < 2048000 THEN 'Less Than 2MB'
WHEN VI_File_Len < 5120000 THEN 'Less Than 5MB'
WHEN VI_File_Len < 10240000 THEN 'Less Than 10MB'
WHEN VI_File_Len < 102400000 THEN 'Less Than 100MB'
WHEN VI_File_Len < 512000000 THEN 'Less Than 500MB'
WHEN VI_File_Len < 1024000000 THEN 'Less Than 1GB'
WHEN VI_File_Len < 2048000000 THEN 'Less Than 2 GB'
WHEN VI_File_Len < 5120000000 THEN 'Less Than 5 GB'
WHEN VI_File_Len < 10240000000 THEN 'Less Than 10 GB'
WHEN VI_File_Len < 15360000000 THEN 'Less Than 15 GB'
WHEN VI_File_Len < 204800000000 THEN 'Less Than 20 GB'
WHEN VI_File_Len < 358400000000 THEN 'Less Than 25 GB'
ELSE 'Larger Than 25GB' END)  AS category
FROM   Volume_Information) derivedtbl_1
GROUP BY category


Regards,

-Ron

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Select Statement returning incorrect information

Richard Hipp-3
On 4/11/17, Ron Barnes <[hidden email]> wrote:
>
> I have been pulling my hair out trying to figure out where I went south.  If
> someone could, would you point out my mistake, please?

What does this query show:

  SELECT DISTINCT typeof(VI_File_Len) FROM Volume_Information;

And in particular, does it show that some of the VI_File_Len values are text?
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Select Statement returning incorrect information

Ron Barnes
In reply to this post by Ron Barnes
I figured it out.
I needed to add the Cast parameter.

SELECT        category, COUNT(*) AS Expr1
FROM            (SELECT        (CASE
WHEN CAST(VI_File_Len AS INTEGER) < 1024000 THEN 'Less Than 1MB'
WHEN CAST(VI_File_Len AS INTEGER) < 2048000 THEN 'Less Than 2MB'
WHEN CAST(VI_File_Len AS INTEGER) < 5120000 THEN 'Less Than 5MB'
WHEN CAST(VI_File_Len AS INTEGER) < 10240000 THEN 'Less Than 10MB'
WHEN CAST(VI_File_Len AS INTEGER) < 102400000 THEN 'Less Than 100MB'
WHEN CAST(VI_File_Len AS INTEGER) < 512000000 THEN 'Less Than 500MB'
WHEN CAST(VI_File_Len AS INTEGER) < 1024000000 THEN 'Less Than 1GB'
WHEN CAST(VI_File_Len AS INTEGER) < 2048000000 THEN 'Less Than 2 GB'
WHEN CAST(VI_File_Len AS INTEGER) < 5120000000 THEN 'Less Than 5 GB'
WHEN CAST(VI_File_Len AS INTEGER) < 10240000000 THEN 'Less Than 10 GB'
WHEN CAST(VI_File_Len AS INTEGER) < 15360000000 THEN 'Less Than 15 GB'
WHEN CAST(VI_File_Len AS INTEGER) < 204800000000 THEN 'Less Than 20 GB'
WHEN CAST(VI_File_Len AS INTEGER) < 358400000000 THEN 'Less Than 25 GB'
ELSE 'Larger Than 25GB' END)  AS category
FROM   Volume_Information) derivedtbl_1
GROUP BY category



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Ron Barnes
Sent: Tuesday, April 11, 2017 9:15 PM
To: 'SQLite mailing list' <[hidden email]>
Subject: [sqlite] Select Statement returning incorrect information

Hello All,

With the select statement below and my test data of 43 files, I expected the following results

22 'Less than 1MB'
4 'Less than 5MB'
7 'Less than 10MB'
4 'Less than 15MB'
6 'Less than 20MB'

Instead I get
16 'Less than 1MB'
18 'Less than 5MB'
9 'Larger than 25GB'

I have been pulling my hair out trying to figure out where I went south.  If someone could, would you point out my mistake, please?


SELECT        category, COUNT(*) AS Expr1
FROM            (SELECT        (CASE
WHEN VI_File_Len < 1024000 THEN 'Less Than 1MB'
WHEN VI_File_Len < 2048000 THEN 'Less Than 2MB'
WHEN VI_File_Len < 5120000 THEN 'Less Than 5MB'
WHEN VI_File_Len < 10240000 THEN 'Less Than 10MB'
WHEN VI_File_Len < 102400000 THEN 'Less Than 100MB'
WHEN VI_File_Len < 512000000 THEN 'Less Than 500MB'
WHEN VI_File_Len < 1024000000 THEN 'Less Than 1GB'
WHEN VI_File_Len < 2048000000 THEN 'Less Than 2 GB'
WHEN VI_File_Len < 5120000000 THEN 'Less Than 5 GB'
WHEN VI_File_Len < 10240000000 THEN 'Less Than 10 GB'
WHEN VI_File_Len < 15360000000 THEN 'Less Than 15 GB'
WHEN VI_File_Len < 204800000000 THEN 'Less Than 20 GB'
WHEN VI_File_Len < 358400000000 THEN 'Less Than 25 GB'
ELSE 'Larger Than 25GB' END)  AS category
FROM   Volume_Information) derivedtbl_1
GROUP BY category


Regards,

-Ron

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Select Statement returning incorrect information

Simon Slavin-3

On 12 Apr 2017, at 2:27am, Ron Barnes <[hidden email]> wrote:

> I needed to add the Cast parameter.

Assuming you are actually storing integers, it might be better if you declared that column as integer in the first place.  Then you wouldn’t need the CAST.

However, well done for figuring it out, possibly with Richard’s help.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Select Statement returning incorrect information

Ron Barnes
Hello Simon,

Got that one on my own!

The Data was created long before me but I do have the option to alter columns if needs be.

Hoping Richard can help out on the Date Select I'm struggling with!

Regards,
-Ron

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Tuesday, April 11, 2017 9:31 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Select Statement returning incorrect information


On 12 Apr 2017, at 2:27am, Ron Barnes <[hidden email]> wrote:

> I needed to add the Cast parameter.

Assuming you are actually storing integers, it might be better if you declared that column as integer in the first place.  Then you wouldn’t need the CAST.

However, well done for figuring it out, possibly with Richard’s help.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...