I've been programming for several years, but I'm pretty new to SQL(ite).
I'm designing a database for a sort of a bookkeeping program. What I've got so far:
orders (o INTEGER PRIMARY KEY, customer INTEGER REFERENCES customers(c), num_products INTEGER, date STRING, discount INTEGER, comments TEXT, status INTEGER) o_products (o INTEGER PRIMARY KEY REFERENCES orders(o), p_0 INTEGER, p_1 INTEGER, p_2 INTEGER, etc. o_qtys (o INTEGER PRIMARY KEY REFERENCES orders(o), q_0 INTEGER, q_1 INTEGER, q_2 INTEGER, etc. o_prices (o INTEGER PRIMARY KEY REFERENCES orders(o), p_0 INTEGER, p_1 INTEGER, p_2 INTEGER, etc.
The customers and products tables are irrelevant.
Now, this design works well for retrieving the products, quantities and prices in an order, but I'm not sure how to do something like figure out total quantity of a product ordered in all orders. I know I could run a lot of queries and sort through the data in my code, but I'm looking for one elegant query, something like SELECT sum(ordered of this product). I could turn the o_products table on its side, to list what orders each product is in, but then it would be hard to retrieve what products are in one specific order. I've done some searching, but I can't seem to find an answer.
Is there a good way to do this, or should I redesign the database? If I should redesign it, how?