Have a products table with item_id and color_id. I’m trying to get the color_id with the most non-null instances. This fails: with This Returns I am looking for color_id 3, which has the most instances. Is there a quick and easy way of getting what I want without 2 queries? Answer This will give you the color_id and the count
Tag: group-by
getting “No column was specified for column 2 of ‘d'” in sql server cte?
I have this query, but its not working as it should, when I run this, I am getting Msg 8155, Level 16, State 2, Line 1 No column was specified for column 2 of ‘d’. Can any one tell me what am I doing wrong? Also, when I run this, I get Msg 8155, Level 16, State 2, Line 1
Counting number of records hour by hour between two dates in oracle
I need a SINGLE query that does this sequence in oracle. As you see the hour is increasing one by one. here is the output I have written a query but it does not give me the right answer! this query gives me a result set that sum of it’s count(*) is equal to the first query written above! here
SQL query with avg and group by
I have some problems with writing a SQL query for MySQL. I have a table with the following structure: mysql> select id, pass, val from data_r1 limit 10; +————+————–+—————…
JOIN two SELECT statement results
Is it possible to join the results of 2 sql SELECT statements in one statement? I have a database of tasks where each record is a separate task, with deadlines (and a PALT, which is just an INT of days from start to deadline. Age is also an INT number of days.) I want to have a table which has
Return all duplicate rows
I’ve written this code to find duplicates and it works fine: The problem is, it’s returning just one of the duplicate rows. Is it possible to return all the duplicate rows? I’m guessing it may have something to do with the ‘GROUP BY’ but I’m not sure how to change it. I don’t want to delete the values, just return
What does SQL clause “GROUP BY 1” mean?
Someone sent me a SQL query where the GROUP BY clause consisted of the statement: GROUP BY 1. This must be a typo right? No column is given the alias 1. What could this mean? Am I right to assume …
How to zero out all negative numbers in a group-by T-SQL statement
I have a T-SQL query where I want all negative quantities to be zeroed out. SELECT p.productnumber, v.[Description], SUM(i.Quantity) as quantity FROM … LEFT JOIN … LEFT JOIN … LEFT JOIN … …
SQL query for finding records where count > 1
I have a table named PAYMENT. Within this table I have a user ID, an account number, a ZIP code and a date. I would like to find all records for all users that have more than one payment per day with …
SQL Query to get column values that correspond with MAX value of another column?
Ok, this is my query: When it pulls the data, I get the correct row for the video_id, but it pulls the first row for each category for the others. So when I get the max result for the video_id of category 1, I get the max ID, but the first row in the table for the url, date, title,