Questions tagged [count]

COUNT : an aggregate SQL function that is used to count the number of rows.

Filter by
Sorted by
Tagged with
2
votes
3answers
478 views

How to merge multiple query results into one

1. SELECT COUNT( * ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '' GROUP BY prtner_name 2. SELECT SUM( inscan ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '...
13
votes
1answer
4k views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
12
votes
2answers
10k views

Why is count(*) slow, when explain knows the answer?

This query: select count(*) from planner_event takes a very long time to run - so long, I gave up and killed it before it finished. However, when I run explain select count(*) from planner_event, I ...
1
vote
1answer
195 views

Count 2 tables same query - PG 9.3

I've got two tables that I need a count on both of them. table1: id table2: cid (table1.id = table2.cid) -- Join can be done by this. I already got two queries that I'm using, and wanna put them in ...
4
votes
1answer
25k views

count multiple occurences of attribute values for each row in sql

I have a mytable structured as follows and I would like to count occurences of values for attribute in each row: id | attribute -------------- 1 | spam 2 | egg 3 | spam With SELECT id, attribute,...
1
vote
4answers
115 views

Counting orders w/ new items

Scenario: Find out any customers in each factory that have three or more orders which contain new items they have never purchased before. For example, John has: Order A: Item 1, Item 2 Order B: Item ...
1
vote
1answer
5k views

MySQL: SELECT with SUM() and COUNT() as new columns

What is the correct SELECT statement to produce this desired output? +------------+----------+-------------+-----------------+ | QtyOfParts | FamilyID | NumInFamily | TotalQtyOfParts | +------------+-...
0
votes
2answers
2k views

Super slow MariaDB 5.5 count(*) on large simple table

I'm doing some testing on a dedicated mediocre server (Core 2 2G ram). Before I upgrade to 8G ram, I am concerned something is very wrong here. Simple queries have normal performance: MariaDB [...
1
vote
2answers
934 views

Count per week per column PostgreSQL

Let's say I have a PostgreSQL table with 10 columns and 2 weeks (w1 w2) with 5 countries. Something that would look like this : What I would like to do is a count per week and per column like this :
5
votes
2answers
25k views

Distinct Combination of Two Columns

I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other. The data is all around backup policies being run on against ...
2
votes
2answers
5k views

Complex joins in multiple subqueries

Sorry to keep bugging you guys, but SQL is still new to me. I have these tables: Store_location, Product, Sizes, Sells, Available_in, Offers, and Currency. The goal is to be able to run one query with ...
58
votes
4answers
36k views

What is the difference between select count(*) and select count(any_non_null_column)?

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table. What are the differences between these ...
1
vote
4answers
112 views

Query slows with additional FROM item (even with no extra actions)

I have a locally hosted table comment on Postgres 9.5 with ~ 2.6 million rows. When I run the following query, it executes in .867 ms: SELECT COUNT(body) FROM comment WHERE body LIKE '%[deleted]%'; ...
1
vote
1answer
564 views

Mysql Count(*) incorrect value

Alrighty so I'm trying to setup a procedure to do a count of locations returned from a geosearch. Geo-search for a specific area returns 2640 records (with a limit of 5000, and offset of 0) but the ...
9
votes
3answers
60k views

How to make multiple counts in one query?

I count records with queries like SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%' SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%' SELECT COUNT(col1) FROM table1 WHERE col1 ...
2
votes
2answers
6k views

How do you count the occurrences of an anchored string using PostgreSQL?

If I have a string in a column on a row in a table like this 1 2 2 2 2 2 2 How would I count the occurrence of a substring 2 inside the string. Assume nothing other than a space-delimiter of " ". ...
5
votes
3answers
178 views

Oracle and MS-SQL-like row counts

My applications often need an accurate but perhaps imperfect row count. Billion-row tables often get frequent writes, so "perfect" is a matter of definition and timing. Microsoft SQL keeps very good ...
1
vote
2answers
3k views

How to select random row with count()

Table names: g_number name 123 John 123 Sara 123 Michael I want to get one RANDOM name and quantity of all names with the same g_number in one query: SELECT name, count(*) cnt ...
1
vote
1answer
32 views

Sum of different table counts

I have 2 different query and i want to summarize their count in other query like this: select a,((select count(id) from foo) + (select count(id) from bar)) as 'alpha' from beta; Is there a ...
3
votes
2answers
2k views

What is an Effective Way to Count the Number of Pages in Oracle?

We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results. We implement paging by putting the original query inside a SELECT that ...
3
votes
3answers
10k views

Count multiple tables as one count

I have seen how to do multiple counts for different tables, but never how to combine them all into one. I have a MySQL DB where I am running the following query: SELECT characters.name, COUNT(*) AS ...
0
votes
1answer
83 views

Get count of relationship rows between three tables that are not duplicates

I have a (local) MySQL database, which is a Drupal 6 export. It has these relevant tables: node - Contains articles, plus also information about their department and sections. I'm interested in nid ...
0
votes
2answers
116 views

Getting count(column) as well as a specific row in each group

I am working with SQL Server 2012. I want to get the count of column as well as the rows with specific condition in each group. The query I have in mind looks something like this: select count(...
0
votes
2answers
4k views

How can I get distinct counts with different where clauses on the same field? [closed]

How can I get DISTINCT row COUNTs with different conditions in a single SELECT statement, and if count is zero get zero as a result? I tried the following query: SELECT DISTINCT A.DeviceFilter, ...
1
vote
1answer
655 views

row count incorrectly displayed as 0 in redshift

I have a table in redshift with columns user_id and some_property, neither of which are unique. The following query returns 0: SELECT COUNT(DISTINCT user_id) FROM table WHERE user_id NOT IN (SELECT ...
4
votes
2answers
7k views

Count with where clause

Is there a way to have WHERE clause in COUNT ? Or how to count with some clause ? select a b, count(c where t1.u='UAE') as c1 from t1 I am using MS SQL Server 2014
0
votes
1answer
67 views

Entries count by date is giving wrong results

I am getting some strange results with below queries: SELECT count(id) FROM `asins` WHERE `StockUpdated` >= '2016-12-30' and `StockUpdated` <= '2016-12-31' The result is : 24446 SELECT count(...
-1
votes
1answer
34 views

How to count records generated by following query?

How can I get the total count of records generated by following query? SELECT AccountA.Name AS 'Account_Name', AccountA.Industry AS 'Account_Industry', AccountA.Key_Account__c AS 'Key_Account', UserA....
20
votes
6answers
4k views

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating ...
0
votes
1answer
2k views

Add '%' sign to MySQL results

I am using a query to calculate total percentage but I want a '%' symbol to appear next to it. Any ideas that could help me My query so far is SELECT student.StudentID, student.`Name`, ROUND (Count(...
1
vote
1answer
2k views

Using DISTINCT and COUNT in a query

I'm having issues using DISTINCT and COUNT in the same query. I would like to get unique items then get a total count for each month. (I've done this in two queries but I know there's a better way.) ...
1
vote
2answers
36 views

Identifying values that are not existent in other table

I've got two tables that have one to many associations on a pmid. So if one table has an pmid, the second table should have multiple rows with the same pmid. However, something went sideways and I'm ...
1
vote
1answer
48 views

Postgres - how to avoid unnecessary comparisons inside a WHERE with a JOIN?

Background We're running this query: SELECT COUNT(*) AS "__count" FROM "xfiler_document" INNER JOIN "xfiler_tx" ON ("xfiler_document"."tx_id" = "xfiler_tx"."id") WHERE ( "xfiler_document"."...
6
votes
3answers
316 views

How to count the number of rows by datetime field with the division into time intervals

I'm using Oracle Database 10g. There is a table with information about clients: +----+------+---------------------+ | id | name | registered_at | +----+------+---------------------+ | 1 | ...
1
vote
2answers
10k views

MySQL count multiple columns and sum the total occurrence

I have the following table, say, MyDRAW ... DRAWNO PRIZENO FIRSTD SECONDD THIRDD FOURTHD M123/14 8458 8 4 5 8 M123/14 7214 7 2 1 4 M123/14 1640 1 6 4 0 ...
2
votes
1answer
2k views

how to sort by timestamp field while counting duplicates

I have a table with username, timestamp and action columns. I need to retrieve a list of usernames, total occurrences of each user and the latest timestamp for each user. The following statement works ...
1
vote
3answers
84 views

How to add a summary count to this query

I am using Postgres 9.4 and I have a query that looks like the following: SELECT attendees.event_id, attendees.user_id, users.tags FROM users, attendees WHERE 'crystal:promo' = ANY(users.tags) AND ...
3
votes
1answer
3k views

Speed up count queries on a couple million rows

Assuming a db full of products. A product can belong to exactly 1 collection and is created by a user. Rough scale of the db: Products: 52.000.000 Collections: 9.000.000 Users: roughly 9.000.000 as ...
1
vote
2answers
50 views

Two separate column counts grouped by one of those columns

I have a Voters table, which has fields including party and email. party takes just a few acceptable values. I have a query which counts party membership and groups by the party: SELECT party as ...
3
votes
2answers
2k views

count null values of table from information_schema tables

Is there any way to count null values in a column by using information_Schema tables? EG: SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='myschema' and ...
3
votes
1answer
1k views

Getting duplicate results in many to many query

I have designed a bookstore app with these tables. There's a book with an M2M relation to author. Lastly, there's table reader which keeps track of which user has read the book i.e. an entry in the ...
14
votes
1answer
12k views

Why is this sqlite query much slower when I index the columns?

I have a sqlite database with two tables, each with 50,000 rows in, containing names of (fake) people. I've constructed a simple query to find out how many names there are (given name, middle initial, ...
0
votes
1answer
1k views

counting the number of a specific word in one SQL column

I'm trying to make a survey like a referundum to know if my neighboors in my small building are for or against a colectif compost to reduce the organics wastes. For this tool vote I'm using PHP and ...
0
votes
1answer
38 views

Mysql query to get count result and group by

I've got three tables with different records for users, User username | realname | date evn-az-3ju john 11/2012 03:09:40 p.m. jwyvm_rdyt steve 12/2012 ...
5
votes
3answers
2k views

MySQL vs PostgreSQL: Benchmarking COUNT(*) execution speed

I benchmark DBs to find out the best for my project and I found that count(*) is extremely slow in PostgeSQL. And I don't understand is it a normal behaviour of PostgeSQL or I do something wrong. I ...
1
vote
1answer
595 views

Select count question with where clause

I have a initial table where I have the following rows: ID_0, geom and utilisatio (where the value varies from 1 to 7). I am trying to create a table where I would have the count of a specific ...
0
votes
1answer
2k views

Select countries and counts of people per country

I have 2 tables, one is a list of countries (id, country) and another is the users (where the country column is the id of the country in the countries table). Is it possible to make a single query ...
3
votes
2answers
230 views

T-SQL Count in single select

I have a table called Catalog. It has multiple columns, for example CreatedByID and ModifiedByID that contain user IDs. I would like to get count of how many documents a user has created and edited. ...
0
votes
1answer
2k views

Count for many-to-many relationship

I have the following tables: surveys properties owners_properties owners ---+------------ ---------- ------------+--------- ------ id | property_id id ...
0
votes
1answer
105 views

SELECT records based on return of COUNT query

I would like to create a query that returns records if values in a column occur more than once. I came across other Stack Exchange questions but these only dealt with returning the number of ...