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'
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 ...
Main problem is the missing index. But there is more.
SELECT user_id, count(*) AS ct
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 ...
You are just missing the GROUP BY
YOUR QUERY WITH GROUP BY
COUNT(attendance.AttendanceID) AS Total
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY student.StudentID,student.`Name`;
DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct
-- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct -- simpler
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 ...
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 ...
To get a count for each of those you can try
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
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:
Even if you did select count(id), it might still take a very long time, unless you have a secondary ...
This is how I'd do it:
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 ...
Similar to Aaron's solutio, shorter syntax:
SUM(col1 LIKE '%something%') AS count1,
SUM(col1 LIKE '%another%') AS count2,
SUM(col1 LIKE '%word%') AS count3
The LIKE expression makes for a boolean result. TRUE is 1, FALSE is 0, so the CASE is redundant here.
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 ...
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.
Is Postgres smart enough to pick up that a SERIAL PRIMARY KEY is going
to exist in every row and never be false
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'.
Postgres reads all the rows FROM data WHERE datetime < '2015-09-23 00:00:00'
Postgres then count(*)s them
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 ...
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.
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 ...
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 ...
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 ...
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).
count(case when t1.u = 'UAE' then c else null end) as c1
group by a, b ;
Note that the ELSE NULL is redundant and can be removed as that is the default ELSE behaviour ...
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
sum(case when a.doubles = 2 then 1 else 0 end) as doubleDoubles,
Possible with a single SELECT:
SELECT name, count(*), to_char((count(*) * 100.0
/ sum(count(*)) OVER ()), 'FM990.00" %"') AS percent
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 ...
Not out of the box. But you can achieve it with a ...
CREATE INDEX tbl_name_hello_idx ON tbl(tbl_id) WHERE name LIKE 'hello%';
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 ...
(select count(*) from mytable m2 where m2.attribute = m1.attribute)
join (SELECT attribute, COUNT(attribute) as c FROM mytable GROUP BY attribute) m2
on (m1.attribute = m2.attribute)
A better version for databases with ...
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
Unless I am missing something, your query would be something like this:
select created, count(*) CreatedCount
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
group by trunc(created)
order by trunc(...
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.
This is an alternative formulation of Travis's answer which avoids the need to sort the COUNT in both directions.
AS (SELECT MyGroup,
Count(MyGroup) AS [Count],
MAX(Count(MyGroup)) OVER () AS [MaxMyGroup],
MIN(Count(MyGroup)) OVER () AS [MinMyGroup]
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
( SELECT COUNT(*) AS number_of_nodes
FROM treenode AS t
WHERE t.project_id = 1
AND t.user_id = u.user_id
-- WHERE c.number_of_nodes > 0 ; -- you probably want ...
DATE_FORMAT(registDate, '%m-%Y') AS month,
COUNT(name) AS register,
SUM(!ISNULL(visited)) AS visited,
SUM(ISNULL(visited)) AS not_visited
GROUP BY DATE_FORMAT(registDate, '%m-%Y');
No need to create another column.