I have a table that looks like this: I want to aggregate by FileID and split the File Info column into 2 separate count columns. I want 1 column to have the count of the Unique File Info and the other to be a count of non-Unique file info. The result would ideally look like this: where the non-unique count
Tag: count
Oracle SQL Developer – group by and count within specific date
SQL noob, trying to get some help grouping by unique name, providing a count, filtered for only start dates during 2021. This is what I have so far, and why I’m getting lost. my_table: Trying to get count of the Name, filtered for only 2021 start dates. This is what I want my output to look like: My code: But
How does one get the total rows for a partition in postgresql
I’m using a windows function to help me pagination through a list of records in the database. For example I have a list of dogs and they all have a breed associated with them. I want to show 10 dogs from each breed to my users. So that would be That will give me ~ten dogs from each breed.. What
COUNT to return NULL as 0 in same table in SAS PROC SQL with GROUP BY
I am having a task where I have 1 table. There is a unique ID for transactions, transaction dates, and store names. My task would be to count the number of transactions every store made during March. This, in itself, is simple: This would lead to a table where a store is present only if it had a transaction in
How to add data to a field based on other fields in a SQL database
I have an SQLite table called wcvp constructed from a csv file downloaded from the World Check List of Vascular Plants (see https://wcvp.science.kew.org/ and http://sftp.kew.org/pub/data-repositories/WCVP/). When I run this query: I get this result: kew_id genus species infraspecies 304293-2 Quercus robur 77189540-1 Quercus robur broteroana 77189379-1 Quercus robur brutia 77189383-1 Quercus robur imeretina 60459295-2 Quercus robur pedunculiflo 77171868-1 Quercus robur
SQL logic to fail a check if any of the related customers has failed
I have the requirement to flag the customers Y only when all the related customers have also passed the check. below are the two tables: relationship table : Check table I want output like below: output justification: since 1,2,3 are related customers and since one of them (3) has n in table 2 , so all the related customers should
need an additional column in SQL output
I have a table called product_info where there are two columns(product, product_id).There are 5 ‘product_id’ and 10 ‘product’. I wrote the following code to list products and their count.Now I need to create an additional column called ‘favorite_product’ if the ‘product’ count is more than 3.When I tried with a couple of WHERE clause options, it filtered out my existing
Select values for a column only if there are no rows with that value that satisfy a condition
I have a table that stores logs of http fetches, let’s say we have two columns: fetch_url and success. A few example rows: success fetch_url TRUE A FALSE A FALSE B FALSE B FALSE …
Lookups in for single PostgreSQL table suddenly extremely slow after large update
I have a messages table with a few million records in it. My Rails app includes a query on most pages to count the number of unread messages to show the user. This query – and all queries of the messages table – is unchanged and was working fine until yesterday. Yesterday, I created a new messages column and ran
SQL query filter down to only applicable rows
How would I select a query like this? I am a bit confused because this would return nothing since any of the rows do not fulfill any of the “where clauses”.. What I would like is the answer …