I’ve got a table which contains event status data, similar to this: ID Time Status —— ————————– —— 357920 2019-12-25 09:31:38.854764 1 …
Tag: count
How to combine two queries with case statement
How to combine two queries with case statement query1 query2 Expected output: Answer With conditional aggregation: The conditions where by_user = ‘3’ or where by_user <> ‘3’ are checked with a CASE expression, so they are removed from the WHERE clause.
Count of line item in MySQL
I have a complex query joined to multiple tables to show a “transaction history”, I want a quick and easy way to count the occurrences of order_id to make it as the line_number. I had it done with code but for performance reason I want to have it done with MySQL. Here is an example of what I have:…
Get comments per video and total sum of comments per video channel
In addition to Get total sum of video comments for each video I noticed that I can’t use v.commentCount because I cleaned some data and now the column is inaccurate. So I need to count the videoId comments and the total videoID comments with the comment table. I have a table video and a table comments. …
How to count two different columns in sql
I have a database which looks like this. My task is to get the columns of dates, number of exams on that date, number of students who write an exam on that date. Something like Date – Exam Count – Student Count. I have done this but in two different queries. Result: edate Exams | =================…
SQL Server How to get number of unique values in a column and average score per values?
I have a table like this and so on What I ve done I got distinct count for every city and avg metric value But it is false Appreciate any help updated There is also an additional column id in varchar format Answer The answer here depends on this assumption: You always have exactly 3 metrics per ‘group&#…
How to fix aggregate functions are not allowed in GROUP BY?
I have the following data: Then i do the following query: The result is correct: Then i add two column, name and value: That return: What i expect is: If i add group by on guid_count, cuid_count, and umdid_count: How to overcome this problem? Could you help point me where my mistake is? Thank you. Answer You …
Creating a view (virtual table) which display colums with particular requirements
So I`m trying to make a statistic view which counts various things like how many users are in table called “PACIENTAS” , how many doctors are in table “GYDYTOJAS” and it works. CREATE OR REPLACE …
How to display the related records in a single row in Oracle SQL?
I wrote a query joining two tables and I got a below resultset: However, I need to display below output with one new field called ‘Status’.Mark can work in both the departments and so the count is “2” and the status will be ‘Y’ ( displaying of any one record is okay) . Just…
How can I optimise COUNT with GROUPBY mysql query?
I have 5M records in eus table and 121 records in es table. I am doing a left join but the COUNT query is making my query very slow. How can I optimize this? public static function …