Hot answers tagged

117

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....


52

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 ...


34

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 ...


30

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 ...


28

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 ...


27

SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct -- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct -- simpler 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 ...


21

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 secondary ...


19

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 ...


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 ...


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

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 ...


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....


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 ...


11

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 ...


10

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 ...


10

I would form groups with the window function count() and then take the first value for each group: SELECT foo_label , first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) AS fixed_foo_price , foo_date FROM ( SELECT foo_label , count(foo_price) OVER (PARTITION BY foo_label ORDER BY foo_date) AS grp , ...


9

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, ...


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

Try SELECT DATE_FORMAT(registDate, '%m-%Y') AS month, COUNT(name) AS register, SUM(!ISNULL(visited)) AS visited, SUM(ISNULL(visited)) AS not_visited FROM mytable GROUP BY DATE_FORMAT(registDate, '%m-%Y'); No need to create another column.


8

I have a very aggressive approach using brute force Dynamic SQL SET group_concat_max_len = 1024 * 1024 * 100; SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database, COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;') INTO @sql FROM (SELECT table_schema db,table_name tb FROM information_schema....


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

This is an alternative formulation of Travis's answer which avoids the need to sort the COUNT in both directions. WITH s AS (SELECT MyGroup, Count(MyGroup) AS [Count], MAX(Count(MyGroup)) OVER () AS [MaxMyGroup], MIN(Count(MyGroup)) OVER () AS [MinMyGroup] FROM MyTable ...


7

I'd first add an index on (project_id, user_id) and then in 9.3 version, try this query: SELECT u.user_id, c.number_of_nodes FROM users AS u , LATERAL ( SELECT COUNT(*) AS number_of_nodes FROM treenode AS t WHERE t.project_id = 1 AND t.user_id = u.user_id ) c -- WHERE c.number_of_nodes > 0 ; -- you probably want ...


7

To count how many contributors have contributed 5 images or more: SELECT COUNT(*) AS number_of_contributors FROM ( SELECT 1 FROM images GROUP BY contributor_id HAVING COUNT(*) >= 5 ) AS t ; It could be written without the derived table but it's obfuscated: SELECT COUNT(*) OVER () AS number_of_contributors FROM images GROUP BY ...


7

This is a classic example of how a "Numbers table" can really help get the results you need. Essentially, you create a table containing the 15 minute increments you desire, then join your table to obtain an aggregate number of calls for each 15 minute increment. In example, I'm using temporary tables for both tables. You'd likely want to make the #...


7

Back when mysql was not transactionally sound by default (when people regularly used myISAM tables instead of InnoDB because that was the default or, going further back in time, because it didn't exist yet) "SELECT * FROM some_table" without any filtering clauses was one of the query types that peopel banged on about mySQL being much faster at that other ...


7

Because your form is off, what you want is SELECT count(DISTINCT x) FROM generate_series(1,10) AS gs(x); Or, in your case, SELECT start_time::date, count(DISTINCT user_id) FROM mytable GROUP BY start_time::date;


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