I have a table with this schema: I need to know percentage of failure for example: google -> 50% failure Facebook -> 100% failure I use mysql 5.7 I tried to reach this destination using group by, but I couldn’t Answer You can do conditional aggregation. I like to do this with avg(): For each provi…
Tag: group-by
Using sum with multiple dates but keeping only the latest date
I have a table called Prospect that looks like this and I’m trying to SUM the value so that I have only 1 column for GEORGIA, ALABAMA,TEXAS and the date kept in the date field be the latest date entry. I would like to have a resultset that looks like this This is what I’ve tried, but it is not
SQL Collect duplicates to one place? PostgreSQL
Sorry I’m new here and I’m also new with SQL and can’t really explain my problem in the title… So I have a TV show database, and there I have a Genre column, but for a TV show there are multiple Genres stored, so when I’m selecting all my TV Shows how can I combine them? It needs…
How to COUNT “accounts” only if character/user exist
I have 2 tables – “accounts” and “players” I’m using this query to COUNT accounts Accounts table estructure: Players table estructure: * Let’s say the query counts 10 accounts but only 3 accounts have a character/user created. I would like to know how to count only th…
Aggregate function not accepted inside WHERE clause
I have the following command. With this PostgreSQL schema. After adding AND day_of_week=3 to WHERE I get the following error. Why cannot I restrict this column? Answer You cannot use a derived column in a where clause. A simple solution is a lateral join: Some advice: Do not use NATURAL JOIN. There is nothing…
Summing condition excludes rows without positive sum
I’m having a weird issue where in returning the sum of rows where a condition is true seems to only return rows if that condition is true – or at least, it is excluding a large majority of the rows that would otherwise be returned. Original query – which returns all rows: For each row that w…
Optimize SQLite Query using GROUP BY
I am trying to optimize my query which uses GROUP BY. But can’t it takes too long time. I have a virtual table which contains 3 columns: I want the group to be just as the query below, but it just takes a bit too long to run. For example I tried this first which works pretty good but still
Top-N By Decade for successive decades (in SQL Server)
I’m trying to get a ranked list of Top 5 (i.e. most common) document titles, grouped by decade, for each of the 6 most recent decades. The document titles are non-unique. There could be dozens or even hundreds of documents with the same title in any given calendar year. The following query is as far as …
Identifying products that a customer owns
I want to check whether my customer owns an iPhone, iPad, or Macbook. I am not looking for how many iPhone or iPad or Macbook one customer has, I just want to identify if they own any of them, or if they own any two, or if they own all three. I am thinking of using the CASE WHEN function
Aggregating data with SQL if other values in rows meet certain criteria
I’m pretty new to SQL and am trying to figure out if there’s a way to aggregate the date into new column. For instance, below is a sample set of date. I want to take all the type ‘2’ and create new …