SQL query SUM(rows) AND ORDER BY ASC-CodePudding

I need to query sqlite3 table it's to much for my current sql understanding this is how it looks with pandas, if someone has the time to implement it in SQL will be very helpful.

import pandas as pd
user_input_qty = 2
user_input_type = 'a1'

df1 = pd.DataFrame({'name': ['A', 'B', 'C', 'D'], '1':[0,0,1,1], '2':[0,0,1,1], '3':[0,1,1,1], '4':[0,0,0,1], 'type':['a1','a1','b1','a1']})
df1['total'] = df1.loc[df1['type'] == user_input_type].sum(axis=1)
df1 = df1.sort_values(by='total').dropna().head(user_input_qty)
df1 = df1[['name', 'total']]
print(df1)

Tried the following, but it doesn't work because obviously don't have an idea what I am doing 'weeks are the number of the weeks during the year':

weeks = [str(x) for x in  range(53)]
query = f'''SELECT *, AS Counted FROM "{weeks}" ORDER BY Counted ASC LIMIT "{user_input}"'''

CodePudding user response：

It's not clear what the table is. Hence it's difficult to understand what you are trying to do. It's not also clear what Counted means. You can't write SELECT *, AS Counted. The AS is used to rename one column you want to rename. You can't rename multiple columns all in once. I ran your pandas code, and based on the output I got, the query should look something like this.I am not sure it will properly work but it can be a starting point

   SELECT table.name, (table.1 table.2 table.3 table.4) as total
   FROM Table --whatever the table is
   GROUP BY table.name
   ORDER BY total ASC