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'
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
I would form groups with the window function count() and then take the first value for each group:
, first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) AS fixed_foo_price
, count(foo_price) OVER (PARTITION BY foo_label ORDER BY foo_date) AS grp
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,
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
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 ...
There are several things you can count with COUNT() function:
count(*) : rows
count(col1) : rows where col1 is not null
count(col2) : rows where col2 is not null
count(distinct col1) : distinct col1 values.
count(distinct col2) : distinct col2 values.
count(distinct col1, col2) : distinct (col1, col2) values combinations.
Tested at SQLfiddle:
(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 ...
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.
To count how many contributors have contributed 5 images or more:
SELECT COUNT(*) AS number_of_contributors
( SELECT 1
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
GROUP BY ...
What you have almost works, just remove the distinct and change the > 2 to > 1. The distinct is not necessary as the grouping handles that and the > 2 is looking for things that have at least three entries rather than just two.
drop table tab1;
create table tab1 as (select 1 column_fk_id1,2 column_fk_id2 from dual);
insert into tab1 values (1,2);
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 ...
Generally, if you don't need an exact count, there is a much faster way:
SELECT reltuples::bigint AS estimate
WHERE oid = 'image'::regclass;
Fast way to discover the row count of a table
As a matter of fact, in a DB with concurrent write access every count is an estimate, because the number may be outdated the instant you get it.
select l.email_list_id, l.email_list_name,
count(d.email_uniq_id) as full_count,
count(case when d.blacklist = 0 then d.email_uniq_id end) as white_count,
count(case when d.blacklist = 1 then d.email_uniq_id end) as black_count
from tbl_email_list as l
left join [tbl_email-details] as d on d.email_list_id = l.email_list_id
group by l....
You need conditional aggregation using sum(case):
sum(case when status = 1 then 1 else 0 end) as published, -- only count status 1
sum(case when status = 0 then 1 else 0 end) as unpublished,-- only count status 0
count(*) as totals
group by cat_id
My crystal ball was apparently broken, but now it's fixed :-)
If you ...
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 #...