How to count few category (not count row) using SQL Server
Let say I have a table like this:
country province city people __________________________________ country1 province1 city1 200 country1 province1 city2 150 country1 province2 city3 350 country2 province3 city4 75
I need to show how many countries, how many provinces, how many cities and how many people I have. Then I need to show how many people in each city, each province and each country.
I already tried
Count(Distinct) but it cannot be used with a partition.
You are close, Andi. Count(Distinct...) is the right way to go.
If you want to know how many countries there are, use:
select count(distinct(country)) from yourtable;
How many provinces? Counting cities is similar to province.
select count(distinct(province)) from yourtable;
How many people?
select sum(people) from yourtable;
For people, you cannot just take a count. You will have to sum up all the numbers.
How to count different values into different rows in SQL efficiently , not sure if redshift supports values, hence I'm using the union all to build the table SELECT categories.category_label, COUNT(*) FROM categories LEFT JOIN Do you only have a few hard categories, or will they be searched dynamically for postgresql - this example is for MS SQL Server, but I'm sure you get the idea. In the subsequent pages, we have discussed how to apply COUNT() with various SQL clauses. For those applications, we have used Oracle 10g Express Edition. An important thing about COUNT() function: When the * is used for COUNT(), all records ( rows ) are COUNTed if some content NULL but COUNT(column_name) does not COUNT a record if its field is NULL.
You can try the below approach to get counts as you desired:
CREATE TABLE #YourTable(country varchar(20),province varchar(20),city varchar(20),people int) INSERT #YourTable(country,province,city,people)VALUES ('country1','province1','city1',200), ('country1','province1','city2',150), ('country1','province2','city3',350), ('country2','province3','city4',75) SELECT 'Distinct Countries' AS type, COUNT(DISTINCT country) from #YourTable UNION ALL SELECT 'Distinct province' AS type, COUNT(DISTINCT province) from #YourTable UNION ALL SELECT 'Distinct City' AS type, COUNT(DISTINCT city) from #YourTable UNION ALL SELECT CONCAT('Distinct ', Country,' ', province,' ', city), count(people) FROM #YourTable GROUP BY ROLLUP(country, province, city)
SQL Server COUNT Function By Practical Examples, This tutorial shows you how to use the SQL Server COUNT() function to get the number of In this form, the COUNT(*) returns the number of rows in a specified table. COUNT(*) does not support DISTINCT and takes no parameters. creates a new table named t , insert some data into the table, and query data against it:. There are several ways to get the count of records for a SQL Server table and we will look at a few options in this tip. Basic Usage of SQL Server COUNT Function. COUNT is an aggregate function in SQL Server which returns the number of items in a group. COUNT will always return an INT. COUNT will use indexes, but depending on the query can
GROUP BY with
GROUPING SETS will help you:
SELECT --GROUPING_ID(country,province,city), CASE GROUPING_ID(country,province,city) WHEN 7 THEN 'total' WHEN 3 THEN 'country total - '+country WHEN 1 THEN 'province total - '+province ELSE 'city - '+city END row_title, country, province, city, COUNT(DISTINCT province) province_count, COUNT(DISTINCT city) city_count, SUM(people) people_count FROM YourTable GROUP BY GROUPING SETS( (country,province,city), (country,province), (country), () ) ORDER BY country,province,city
CREATE TABLE YourTable(country varchar(20),province varchar(20),city varchar(20),people int) INSERT YourTable(country,province,city,people)VALUES ('country1','province1','city1',200), ('country1','province1','city2',150), ('country1','province2','city3',350), ('country2','province3','city4',75)
Also try to use the following:
SELECT column_name,value FROM ( SELECT COUNT(DISTINCT province) [province count], COUNT(DISTINCT city) [city count], SUM(people) [people count] FROM YourTable ) q UNPIVOT(value FOR column_name IN([province count],[city count],[people count])) u
Count of rows with the SQL Server COUNT Function, Can you provide any examples? Solution. There are several ways to get the count of records for a SQL Server table and we will look at a few� A ROW_NUMBER() function isn't a practical option: it isn't a counting function (it's "ROW_NUMBER") and it will run badly as you add rows: a few 1000 will show how bad this it. SUM(1) may be optimised to COUNT(*) internally but I'd never use it @@ROWCOUNT will require all rows to be returned by the first SELECT, which is a huge unnecessary overhead.
This following script will return your all results in a single row. But if you want each results in a separate row, you may need to use UNION to achieve the expected result set.
SELECT COUNT(DISTINCT Country) [Number of Country], COUNT(DISTINCT Province) [Number of Province], COUNT(DISTINCT City) [Number of City], SUM(People) [Total People] FROM ( VALUES ('country1','province1','city1',200), ('country1','province1','city2',150 ), ('country1','province2','city3',350), ('country2','province3','city4',75) ) V([Country],[Province],City,People);
Overview of the SQL Count Distinct Function, We use SQL Count aggregate function to get the number of rows in the output. It means that SQL Server counts all records in a table. Let's create a sample table and insert few records in it. SQL COUNT Distinct does not eliminate duplicate and NULL values from the result set. Categories and tips. If the above statement returns any rows, then it will not execute further but if this statement do not have any row then it will execute next statement. I have tried it using in two ways . @@rowcount - it's not working properly; Using temp table by inserting select statement into table getting row count of table but using temp table is not
I don't see why you need
count(distinct) for the population. How about this?
select country, province, city, sum(people) from t group by grouping sets ( (country, province, city), (country, province), (country) );
I see no evidence in your sample data that people are being double counted.
To count the numbers for each group requires something like this:
select count(distinct country) as num_countries, count(distinct country + ':' + province) as num_provinces, count(distinct country + ':' + province + ':' + city) as num_cities
SQL Server does not allow multiple arguments for
Summarize Your SQL Results with the GROUP BY clause, Count occurrences of non-null values returned by the expression. COUNT(*), Counts all rows in the specified table. MIN(expression), Finds the minimum� SELECT ProductNumber , Name , COUNT (*) AS Count FROM Production.Product. The above select statement will of course fail as we have not included a group by and the COUNT function is an aggregate function. But we can use an OVER clause to specify that the count function is a windowing rather than an aggregate function, to do this we change our query to look like the example below.
To return the number of rows that includes the number of duplicates and excludes the number of the NULL values, you use the following form of the COUNT () function: 1. COUNT(ALL column) The following table illustrates all forms of the COUNT () function: COUNT () Function. Count Duplicates.
The SQL COUNT(), AVG() and SUM() Functions. The COUNT() function returns the number of rows that matches a specified criterion. The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column. COUNT() Syntax
The SQL COUNT function is an aggregate function that returns the number of rows returned by a query. You can use the COUNT function in the SELECT statement to get the number of employees, the number of employees in each department, the number of employees who hold a specific job, etc.
- If I am understanding this correctly you want number of countries, number of province, number of city and total population right ? If thats the case for all number of <cities/province/city> do count() and for total peoples do sum()
- Please shown the full query you have tried.