SUM column if other column has identical data python and mysql

Question

I am trying to find a way to SUM data in one column of my datbase on a SELECT statement if the Date column has the same day. My table would have three columns: Ticker, Mentions and Data. Ticker for this example would be BTC, there might be 10 rows and those ten rows have different mention counts(INT) for each one. Then the Date: 4 might have 2021-02-24, 5 might have 2021-02-25 and 1 might have 2021-02-23.

What I would like my SELECT statement to do is check that if the DAY is the same that it SUMs Mentions for that day and returns it as one INT. So in this case 3 INTs would be returned. A sum of the 24th, a sum of the 25th and then the single mention count of the 23.

Currently my SELECT looks like this:

SELECT Mentions 
FROM crypto_currency_trend_data 
WHERE Ticker = '" + ticker + "' AND YEAR(Date) = '" + current_year + "' 
ORDER BY Date DESC

I have read about SUM(IF()) but I am not really sure how to apply it in this situation. Any advice is greatly appreciated.

I have read about SUM(IF()) but I am not really sure how to apply it in this situation. SUM(IF({all conditions}, column_to_sum, 0)). But I'd recommend CASE instead of IF - it looks more clear: SUM( CASE WHEN {all conditions} THEN column_to_sum ELSE 0 END)). And according GROUP BY, of course. — Akina
– Akina, Commented Feb 25, 2021 at 5:45

moosearch · Accepted Answer · 2021-02-25 05:53:57Z

1

Give this sql fiddle a try, Barmar basically stated the answer:

http://sqlfiddle.com/#!9/468645/1

SELECT 
  ticker,
  sum(Mentions) as NumMentions,
  date
FROM 
  crypto_currency_trend_data 
group by 
  ticker, 
  date

answered Feb 25, 2021 at 5:53

moosearch

3242 silver badges12 bronze badges

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

Comments

abysslover · Accepted Answer · 2021-02-25 06:01:15Z

I have tested a code with small exemplary data as follows:

import sqlite3
import pandas as pd
if __name__ == '__main__':
    conn = sqlite3.connect(":memory:")
    data = {"Ticker": ["BTC", "BTC", "BTC", "BTC", "BTC", "BTC", "BTC", "BTC", "BTC", "BTC"],
            "Mentions": [0, 1, 2, 3, 4, 5, 6, 7, 8, 9],
            "Date": ["2021-02-24", "2021-02-24", "2021-02-24", "2021-02-24",
                     "2021-02-25", "2021-02-25", "2021-02-25", "2021-02-25", "2021-02-25",
                     "2021-02-23"]            
            }
    
    df = pd.DataFrame(data)
    # write the tables
    df.to_sql("crypto_currency_trend_data", conn, index=False)
    ticker = "BTC"
    current_year = 2021
    sql = f"""SELECT COUNT(Date) AS COUNT, Date FROM crypto_currency_trend_data WHERE Ticker="{ticker}" AND strftime('%Y', Date)="{current_year}" GROUP BY Date"""
    df_count = pd.read_sql_query(sql, conn)
    print(df)
    print(df_count)

Results:

  Ticker  Mentions        Date
0    BTC         0  2021-02-24
1    BTC         1  2021-02-24
2    BTC         2  2021-02-24
3    BTC         3  2021-02-24
4    BTC         4  2021-02-25
5    BTC         5  2021-02-25
6    BTC         6  2021-02-25
7    BTC         7  2021-02-25
8    BTC         8  2021-02-25
9    BTC         9  2021-02-23
   COUNT        Date
0      1  2021-02-23
1      4  2021-02-24
2      5  2021-02-25

You will be able to adjust the provided according to your needs.

Collectives™ on Stack Overflow

SUM column if other column has identical data python and mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related