6
SQL query - count with subquery
source link: https://www.codesd.com/item/sql-query-count-with-subquery.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL query - count with subquery
advertisements
I want to get the count of all the columns that are retrieved in the query.I have used the below code:
select count (*)
from (
select distinct ID,salary,name,location
from test
) ;
I am getting an error message:
Incorrect error at ; expecting AS,ID or quoted_ID
When I add as below:
select count (*)
from (
select distinct ID,salary,name,location
from test
) as count;
The query works now but the column name is not renamed to the alias given. What is the logic behind this?
In Sql you have to give alias to subquery. So that's the reason the second query works and first one fails
Related Articles
SQL Query COUNT with Two Joins
I'm trying to make a SQL Query from 3 tables (messages, user_comment_messages and user_like_messages), I need to get a list of each message with the total number of likes and comments, actually I'm trying with this: SELECT m.id, COUNT(c.id) AS t_comm
Rewrite the SQL query without subqueries (subquery in a clause)
On my final exams I got pretty nasty SQL, relational algebra and relational calculus queries. I got this query: Find the names of the clients who ordered all products from category 'Computer'. (customers who ordered every product in the computer cate
SQL combining counts with joins
I have three tables: Messages messageid | userid | text Ex: 1 | 1303 | hey guys Users userid | username Ex: 1303 | trantor 1301 | tranro1 1302 | trantor2 Favorites messageid | userid Ex: 1 | 1302 1 | 1301 What I want to do, is display a table that ha
SQL query, count two columns based on the same identifier
I have sql server databse with two tables, teams and games. Each game has a predicted_winner column and a winner column. both columns reference the team table id field. I want to query the database and get a count of how many games the team was predi
The LINQ to SQL query fails with two statements Contains
I have two tables, DH_MASTER and DH_ALIAS. DH_MASTER contains information about a person, including their name. DH_ALIAS contains AKA records about the person. The tables are linked by the Operator field which is a primary key in DH_MASTER. The users
Why does the following linq to sql query generate a subquery?
I did the following query: var list = from book in books where book.price > 50 select book; list = list.Take(50); I would expect the above to generate something like: SELECT top 50 id, title, price, author FROM Books WHERE price > 50 but it generate
Fusion table The SQL query fails with the numbers
I'm making a tiny webapp to see what are the pub's specials around you and I have a very silly problem with my SQL query to fusion table. Here is my table and her is my query: ST_INTERSECTS(address, CIRCLE(LATLNG(-33.898672999999995, 151.2063809), 30
Format a SQL query result with an arbitrary number of decimals
I have a database table with these two columns: Amount: numeric (18,0) DecimalPlaces: numeric (18,0) This table can store amounts in various currencies, with the decimal place removed from the amount (I can't change this data model). For example, the
SQL query help with bridge table
I'm working with a existing database and trying to write a sql query to get out all the account information including permission levels. This is for a security audit. We want to dump all of this information out in a readible fashion to make it easy t
T-SQL migration of Access SQL Query running with a problem writing replacement CASE for IIF
I have two tables BMReports_FPN_Curves and BMReports_BOA_Curves each consisting of a Name, Datetime, Period and Value, for example: BM_UNIT_NAME RunDate Period FPN (or BOA) T_DRAXX-1 2010-12-01 00:03:00 1 497 With the RunDate field incrementing by on
SQL Delete from with subquery error
Evening all, I have the following SQL Query for PDO: DELETE FROM group_members WHERE group_id IN( SELECT * FROM groups WHERE group_owner = 1 ) AND user_id = 2 And for some strange reason I keep getting the following message: #1241 - Operand should co
How to convert SQL select Count with group by clauses in LINQ?
How can I convert the following SQL query into LINQ? SELECT [EmpUserName],[LeaveTypeName],Count([NoOfDays]) as 'Count' FROM [NSL.LeaveSystem02].[dbo].[ProcessedCPLeaves] GROUP BY [LeaveTypeName],[EmpUserName],[ProcessingDate] Please Help. ThanksFollo
SQL Query - Select with Total Column
I have a query that returns rows for current and non-current items. The current items have no value The non-current items do have a value I need my query to return all the current values AND the total value of the non-current items: Sample: Name Stat
Sql query custom with sql with pagination
I've done a custom sql query for my blog. What the code does is to retrive the latest image from a post that has a gallery. In this way i can display latest ... les't say 12 posts that have a gallery- the code actualy displays the first image that is
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK