Hot answers tagged

76

If I understand you correctly, you are looking for a filtered (conditional) aggregate: SELECT a.agent_id as agent_id, COUNT(a.id) filter (where disposition = 'Completed Survey') as CompletedSurvey, count(a.id) filter (where disposition = 'Partial Survey') as partial_survey FROM forms a WHERE a.created_at >= '2015-08-01' AND a....


50

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach. There is one other, tricky way though that works no matter if the table has or not such a ...


26

SELECT (count(refinst) * 100)::numeric / count(*) AS refinst_percentage FROM patients; Do not use a subselect. Both aggregates can be derived from the same query. Cheaper. Also, this is not a case for window functions, since you want to compute a single result, and not one result per row. Cast to any numeric type that supports fractional digits, like @...


25

Main problem is the missing index. But there is more. SELECT user_id, count(*) AS ct FROM treenode WHERE project_id = 1 GROUP BY user_id; You have many bigint columns. Probably overkill. Typically, integer is more than enough for columns like project_id and user_id. This would also help the next item. While optimizing the table definition, consider ...


23

There are various ways to "count" rows in a table. What is best depends on the requirements (accuracy of the count, how often is performed, whether we need count of the whole table or with variable where and group by clauses, etc.) a) the normal way. Just count them. select count(*) as table_rows from table_name ; Accuracy: 100% accurate count at the ...


21

You are just missing the GROUP BY YOUR QUERY WITH GROUP BY SELECT student.StudentID, student.`Name`, COUNT(attendance.AttendanceID) AS Total FROM student LEFT JOIN attendance ON student.StudentID = attendance.StudentID GROUP BY student.StudentID,student.`Name`; SAMPLE DATA DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh; USE ...


18

To get a count for each of those you can try SELECT COUNT(CASE WHEN `col1` LIKE '%something%' THEN 1 END) AS count1, COUNT(CASE WHEN `col1` LIKE '%another%' THEN 1 END) AS count2, COUNT(CASE WHEN `col1` LIKE '%word%' THEN 1 END) AS count3 FROM `table1`;


18

This is how I'd do it: SELECT * FROM #MyTable AS mt CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc FROM #MyTable AS mt2 WHERE mt2.Col_A = mt.Col_A -- GROUP BY mt2.Col_A ) AS ca; The GROUP BY clause is redundant given the data provided in the question, but may give you a ...


16

For starter there is no such thing as the 'current count' to store in a variable. A query like SELECT COUNT(*) FROM ... is subject to the current isolation level and all concurrent pending transactions. Depending on the isolation level, the query can see or not see rows inserted or deleted by pending uncommitted transactions. The only way to answer is to ...


15

Some points As @swasheck mentioned, you can't have a condition like WHERE a = b = c in SQL, it's not valid (unlike other languages). You need to make it WHERE a = b AND b = c Using implict joins with WHERE is not good practise any more, 25 years since SQL-92 standards adopted the JOIN syntax (a JOIN b ON <condition>), which has several advantages and ...


15

Similar to Aaron's solutio, shorter syntax: SELECT SUM(col1 LIKE '%something%') AS count1, SUM(col1 LIKE '%another%') AS count2, SUM(col1 LIKE '%word%') AS count3 FROM `table1` The LIKE expression makes for a boolean result. TRUE is 1, FALSE is 0, so the CASE is redundant here.


15

I got consistent results in my repeated tests with various versions over the last years: count(*) is slightly faster than count(pk). It is also shorter and most of the time it better fits what is tested: the existence of a row. Concerning: Is Postgres smart enough to pick up that a SERIAL PRIMARY KEY is going to exist in every row and never be false ...


14

In SQLite, joins are executed as nested loop joins, i.e., the database goes through one table, and for each row, searches matching rows from the other table. If there is an index, the database can look up any matches in the index quickly, and then go to the corresponding table row to get the values of any other columns that are needed. In this case, there ...


14

Explain is using previously gathered statistics (used by the query optimizer). Doing a select count(*) reads EVERY data block. Here's a cheap way to get an estimated row count: select TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME='planner_event'; Even if you did select count(id), it might still take a very long time, unless you have a ...


14

The indexed view should be among the fastest options, with the lowest maintenance overhead, when implemented optimally. Modifications are incremental (deltas) as I explain in detail in Indexed View Maintenance in Execution Plans (a full recount is not performed on every base table update); however, you do need to ensure that the delta update parts of the ...


13

Books Online states that the rows field "indicates the approximate number of rows in this partition." I would therefore expect it to be close, but not 100% accurate, 100% of the time. Michael Zilberstein reports an example of sys.partitions being wildly incorrect in For want of a nail. Not saying it is a common occurrence, but it is possible. sys....


13

You're limiting the resultset of the aggregate function count(), which will always return 1 row. IE: It's limiting the output of the count(*) function, rather than LIMITing just FROM data WHERE datetime < '2015-09-23 00:00:00'. Basically: Postgres reads all the rows FROM data WHERE datetime < '2015-09-23 00:00:00' Postgres then count(*)s them ...


12

If ForeignId, ForeignTable, IsMain is not known* to be unique in ExternFile, then the QO will need to include that table to work out the count. Any time multiple rows match, the count will be affected. Join Simplification in SQL Server Designing for simplification (SQLBits recording) * The optimizer does not currently recognize filtered unique indexes as ...


11

Integer division truncates fractional digits. Your expression returns a ratio between 0 and 1, which is always truncated to 0. To get "percentage", first multiply by 100. To also get fractional digits, cast to numeric (before you divide) - or multiply by 100.0. The presence of a fractional digit in the numeric literal coerces the result to numeric ...


10

Netezza is an appliance that is designed to excel at large table scans, so that's why you're getting such fast results on that system. For your SQL Server, you can greatly speed up the row count by querying from the sys.dm_db_partition_stats DMV. SELECT s.name AS [Schema], o.name AS [Table], SUM(p.row_count) AS [RowCount] FROM sys.dm_db_partition_stats ...


10

Don't know if this is the best way. I first did a select to find out if a stat is double digit and assign it a 1 if it is. Summed all those up to find out total number of double digits per game. From there just sum up all the doubles and triples. Seems to work select a.player_id, a.team, sum(case when a.doubles = 2 then 1 else 0 end) as doubleDoubles, ...


10

No, the syntax you have is not valid, it can be corrected by the use of a CASE expression. (and I guess you have a GROUP BY a, b as you'd get an error otherwise). select a b, count(case when t1.u = 'UAE' then c else null end) as c1 from t1 group by a, b ; Note that the ELSE NULL is redundant and can be removed as that is the default ELSE behaviour ...


9

I agree with @RemusRusanu (+1 for his answer) SELECT COUNT(*) FROM mydb.mytable in InnoDB behaves like a transactional storage engine should. Compare it to MyISAM. MyISAM If mydb.mytable is a MyISAM table, launching SELECT COUNT(*) FROM mydb.mytable; is just like running SELECT table_rows FROM information_schema.table WHERE table_schema = 'mydb' AND ...


9

Possible with a single SELECT: SELECT name, count(*), to_char((count(*) * 100.0 / sum(count(*)) OVER ()), 'FM990.00" %"') AS percent FROM t GROUP BY 1 ORDER BY 1; count(*) is a separate form of the function and slightly faster than count(<expression>). Assuming all columns to be NOT NULL, else you may have to use the ...


9

Not out of the box. But you can achieve it with a ... Partial index CREATE INDEX tbl_name_hello_idx ON tbl(tbl_id) WHERE name LIKE 'hello%'; SELECT reltuples FROM pg_class WHERE oid = 'tbl_name_hello_idx'::regclass; -- or schema-qualify table name The actual index column (tbl_id in the example) is irrelevant (unless you have additional use for the ...


9

select m1.id, m1.attribute, (select count(*) from mytable m2 where m2.attribute = m1.attribute) from mytable m1 ; Another version: select m1.id, m1.attribute, m2.c from mytable m1 join (SELECT attribute, COUNT(attribute) as c FROM mytable GROUP BY attribute) m2 on (m1.attribute = m2.attribute) ; A better version for databases with ...


8

if you put count(*), count(1) or count("test") it will give you the same result because mysql will count the number of rows, for example: select count(fieldname) from table; will display the same result that select count(*) from table; or select count(1) from table mysql> select * from language; +-------------+----------+---------------------+ | ...


8

You could do something like: select distinct x.id, x.lastname, x.firstname, x.email from t as x join ( select id from t group by id having count(distinct email) > 1 ) as y on x.id = y.Id


7

An alternative view: Are you really using inserts for 2B rows? You might be better off bulk loading the data. 2B rows shouldn't take 3 days unless they are spectacularly wide. Also, pg_bulkload might be of interest for this.


7

Unless I am missing something, your query would be something like this: select created, count(*) CreatedCount from yourtable group by created order by created; See SQL Fiddle with Demo Or if you have a time associated with the date, you can use TRUNC: select trunc(created), count(*) CreatedCount from yourtable group by trunc(created) order by trunc(...


Only top voted, non community-wiki answers of a minimum length are eligible