Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQLite question

SQLite question

SQLite question

Hi, I'm posting in General because I didn't see a specific forum for SQLite.  Here's my question:

I am having difficulty using SQLite.  I have a table, let's say it's called Inventory and its fields are Product and Store, like this:

Pen      |  Bookstore
Soda    |  Drugstore
Journal |  Bookstore
Pen      |  Supermarket
Paper   |  Drugstore
Paper   |  Bookstore

etc., and I want to produce a query that shows, for each object, how many of them there are in each store.

I know I can make a bunch of Case statements, like so:


Select Product,
                   sum(case store when bookstore then 1 else 0) as Bookstore,
                   sum(case store when drugstore then 1 else 0) as Drugstore,
                   sum(case store when supermarket then 1 else 0) as Supermarket
          From Inventory
          Group By Product;

but this isn't good because if I ever add another type of store then I have to change the code.

If I was using Access I could use Transform and Pivot to build the query how I want it, but these don't seem to be available in SQLite, as using them produces syntax errors.

RE: SQLite question



Select Product, Store, Count(*)
From Inventory
Group By Product, Store;

glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: SQLite question

Ok, this gives a result of:

Product     Store       Sum
Pen         Bookstore      1
Pen         Supermarket  1
Paper      Drugstore      1
Paper      Bookstore      1

etc., but the problem is that it doesn't display zero values for Products that don't appear in a particular type of Store, and I also want it to look like an MS Access crosstab query, e.g.:

Product Drugstore Bookstore Supermarket
Pen           0             1           1
Paper        1             1           0

I haven't been able to figure out how to make crosstab queries in SQLite, though.

RE: SQLite question

Create a crosstab in a reporting tool or application, not a database.

RE: SQLite question

in order to get information about things that DON'T exist you need a list of everything that DOES exist..so do you have a table that lists all the store types?



Come join me at New Mexico Linux Fest!

RE: SQLite question

Yes, there is a table that lists all stores.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close