SQLite question

sorkIndustries · May 20, 2009

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:

Product+Store
---------|---------
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:

Code:

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.

SkipVought · May 20, 2009

Hi,

Code:

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

Skip,
^{Don't let the Diatribe...

talk you to death!}_{Just traded in my old subtlety...

for a NUANCE!}

sorkIndustries · May 20, 2009

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.

RiverGuy · May 20, 2009

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

lespaul · May 21, 2009

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?

Leslie

Come join me at New Mexico Linux Fest!

sorkIndustries · May 22, 2009

Yes, there is a table that lists all stores.

lespaul · May 22, 2009

then you need to do something like this:

Code:

Select Product, Stores.Store, Count(*)
From Inventory
LEFT OUTER JOIN Stores on Stores.store = Inventory.Store
Group By Product, Stores.Store;

you may find this helpful
Understanding SQL Joins

Leslie

Come join me at New Mexico Linux Fest!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQLite question

sorkIndustries

Technical User

SkipVought

Programmer

sorkIndustries

Technical User

RiverGuy

Programmer

lespaul

Programmer

sorkIndustries

Technical User

lespaul

Programmer

Similar threads

Part and Inventory Search

Sponsor