I have a postgresql table contains a list of email addresses. The table has three columns, Email, EmailServer (e.g., gmail.com, outlook.com, msn.com, and yahoo.com.ca etc.), and Valid (boolean). Now, I want to group those emails by EmailServer and then update the first 3 records of each large group (count >…
Tag: group-by
Grouping columns to find most popular product for each country
I’m a SQL beginner, practicing through various sources. I have a table called marketing_data containing product sales information, country and other variables where I’m trying to get an output for the most popular product per country, based on sales. I don’t know where to begin with my synta…
SQL query to extract matching diagonal pairs in SQL Server database
I have a database table (mytable) with 2 columns x and y as shown below, from which I intend to extract rows with matching diagonal pairs of (x,y) and (y,x) e.g., 4 21 and 21 4 I have tried the accepted code on stackoverflow here (enter link description here) on my mytable which gives me the expected results …
SQL want efficient query to SELECT columnA, columnB FROM table1 WHERE both columnA, columnB are in columnC FROM table2 WHERE columndD > value
I’m searching for a more efficient method to accomplish something that I’ve already solved with a few different SQL statements. The problem: I have two tables a transactions table, and an accounts table The transactions table has columns that look like: acct_sending acct_receiving amount tx_dateti…
How would I make my SQL statement show only the first line (lowest price + total purchases)
I’ve got this SQL exercise I’m trying to complete but I can’t figure out how to specifically show one row that should contain the media type with the lowest # of purchases and purchase value. I’m completely unsure of what I should add after my ‘group by’? So this is what I …
Count and Group by 2 different columns SQL
I have below table and want to find out a count and group by that will show the number of times republic of ireland played a particular opponent regardless if they are the home team or away team I want something that will show like the below Thanks for help! Answer This is how I would do it — normalize
Can I get two count results in a query?
I am using MariaDB. I want to have two count results in a query. The first query is: Next is: What I want to get is: How can I combine them? Answer The usual way is to make a Case..When statement that is one or zero for the desired condition and then SUM it, not COUNT it.
Number of Customer Purchases in Their First Month
I have a list of customer orders. I can easily calculate the month and year of first purchase for each customer (e.g. customer 1 had their first purchase in Sept 2021, customer 2 had their first purchase in Oct 2021, etc.). What I want to add is an additional column that counts the number of purchases a custo…
Select highest aggregated group
I’m having trouble with selecting the highest aggregated group. I have data in a table like this: Sales table: ID GroupDescription Sales 1 Group1 2 1 Group1 15 1 Group2 3 1 Group3 2 1 Group3 2 1 Group3 2 2 Group1 2 2 Group2 5 2 Group3 3 2 Group4 12 2 Group4 2 2 Group4 2 I want
SQLite: how to exclude records in a database with join tables?
I have a database with a schema similar to: Say they have the following data in them: Artists: artist_id artist_name 1 Bob 2 Shawn Songs: song_id song_name 1 one song 2 another song song_artist: song artist 1 1 1 2 2 2 Here, both Bob and Shawn are involved in one song, and only Shawn is involved in another so…