Python Sqlite3 advanced grouping

Question

I hope this question has not been asked before. I have a csv file containing below columns and info and I can upload this to sqlite3 database with the exact same column names

Company Type Qty Price
ABC      r    5   $$$
ABC      h    9   $$$
ABC      s    10  $$$
DER      a    3   $$$
DER      z    6   $$$
GGG      q    12  $$$
GGG      w    20  $$$

but, how do I get the below result on a csv file? Basically, grouping by the company name and still showing Type, Qty and Price, and at the end of lines showing the total qty for each company.

Company Type Qty Price Total Qty
ABC     r    5   $$$      
        h    9   $$$
        s    10  $$$      24
DER     a    3   $$$
        z    6   $$$      9
GGG     q    12  $$$
        w    20  $$$      32

I have been trying with GROUP BY, but no luck. Any help is much appreciated Thank you

Maybe you can do that with a SQL expression including UNION.But why don't you separate this in two expressions: The first is selecting and ordering the data and the second one is doing the group by and the sum. If you post the code to create and fill the db I can help you further. — schlamar
– schlamar, Commented Jun 6, 2012 at 17:57

David Wolever · Accepted Answer · 2012-06-06 18:18:23Z

3

I would do this using a combination of SQL and itertools.groupby.

For example, something like:

results = cxn.execute("SELECT * FROM orders ORDER BY Company")
for company, orders_iter in itertools.groupby(results, key=lambda r: r[0]):
    orders = list(orders_iter)
    total_qty = sum(order[2] for order in orders)
    ... print out orders ...

Someone with more SQL-foo than I have might be able to come up with a query which would produce exactly the results that you want… But that's probably unnecessary, especially given that, with some minor modifications (calculating the total while iterating over the orders), the Python code would only need O(n) time and O(1) memory.

answered Jun 6, 2012 at 18:18

David Wolever

156k94 gold badges365 silver badges513 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Infinite_Loop Over a year ago

Thank you, it seems to be working. I will play around with this.

schlamar Over a year ago

Instead of creating a list of orders, you can duplicate the iterator with itertools.tee.

David Wolever Over a year ago

@ms4py what advantage will that have? In the example I have, it will still have to store a copy of the list.

David Wolever Over a year ago

@Python_Rocks Cool, no problem. Once you've confirmed it has answered your question, hit the checkbox below the voting-on-the-answer box to accept it (and if you think it's especially good, maybe give it an upvote too).

Collectives™ on Stack Overflow

Python Sqlite3 advanced grouping

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related