sigil Posted May 18, 2009 Share Posted May 18, 2009 I am having difficulty using the _SQLite functions. 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. I know this is more of an SQLite question than an Autoit question, but I haven't been able to find an SQLite forum on the web, and since I'm new to SQL as a whole, I am a bit bewildered. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now