SQLite question

[sorkIndustries]

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:

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]

Hi,

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

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sorkIndustries]

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]

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

 

[lespaul]

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]

Yes, there is a table that lists all stores.

 

[lespaul]

then you need to do something like this:

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!