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 ...
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;
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 ...
SELECT (count(refinst) * 100)::numeric / count(*) AS refinst_percentage
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 @...
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
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 ...
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 ...
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.
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 ...
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 ...
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 ...
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 ...
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,
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 ...
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.
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 ...
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 ...
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;
select count(1) from table
mysql> select * from language;
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(...