SQL COUNT overflow
Use COUNT_BIG
SELECT COUNT_BIG(*) FROM Similarities WHERE T1Similarity = 0 OR T2Similarity = 0
SQL Count Calculation
This is two steps:
- Get the number of unique shops per client.
- Get the average of that number.
The query:
select avg(number_of_shops)
from
(
select client_number, count(distinct retailer) as number_of_shops
from transactions
group by client_number
) clients_with_shop_count;
SQL Count() returns all 1s
Your primary issue is that once you add a column into the GROUP BY
, you get another row for each combination of it.
Also for some reason you are already filtering duplicates using ROW_NUMBER
, even though the grouping would have reduced those rows anyway.
Either way, it seems you just want a windowed COUNT
SELECT
ITEMID,
SECTIONID,
COUNT(*) AS CountPerSectionAndItemId,
COUNT(*) OVER (PARTITION BY SECTIONID) AS CountOfItemIds
FROM mySampleData s
GROUP BY
ITEMID,
SECTIONID
ORDER BY
SECTIONID;
SQL Fiddle
Note that this count happens after grouping. If you want the sum of the count then you need SUM(COUNT(*)) OVER (PARTITION BY SECTIONID)
SQL - How to count rows between two values?
I don't know what DB you are using. But I wrote this SQL query in PostgreSQL, I think that this query will be run in many Databases.
with org_table as materialized
(
select
row_number() over (order by "timestamp") as r_num,
"timestamp",
"value"
from original_table
)
select min(a1.begintime) as begintime, a1.endtime from (
select
t1."timestamp" as begintime,
min(t2."timestamp") as endtime
from org_table t1
inner join org_table t2 on t2.r_num > t1.r_num and t2.value > 0
where t1.value = 0
group by t1."timestamp"
) a1
group by a1.endtime
Result:
begintime endtime
2022-06-03 00:09:16.000 2022-06-03 00:09:29.000
2022-06-03 00:09:44.000 2022-06-03 00:09:55.000
SQL Count() need 0 as well
If all label
s are available in the table, you can use conditional aggregation:
SELECT label,
SUM(user_id = '1' AND start = '1') AS Anzahl
FROM datensammlung
GROUP BY label;
SQL count replies
Table alias (the AS
statement) allows you to refer to a table by an alias you give it, thus enabling you to compare values between same table.
Wrong answer:
UPDATE posts AS table1
SET replies = (SELECT COUNT(1) FROM posts AS table2 WHERE table2.replyto = able1.id)
Correct answer: (with caveats)
It seems you can't use update on same table you are selecting from. The workaround is to wrap the offending query inside another.
UPDATE posts AS table1
SET replies = (SELECT counter FROM
(SELECT COUNT(1) AS counter FROM posts AS table2 WHERE table2.replyto = table1.id) AS table3
)
This will work. But on some situations this might throw you the same error. Read more https://stackoverflow.com/a/45498/3807365
Count distinct based on another column
Assuming this objective:
Truncate dates to the month.
Pick one location per (month, cid), the home location getting priority.
Then count rows per (month, location).
SELECT date, location, count(*)
FROM (
SELECT DISTINCT ON (1, 2) -- choose **one** location per (month, cid)
date_trunc('month', date)::date AS date, cid, location
FROM tbl
ORDER BY 1, 2, birth_place = location DESC -- priority to home location, else **arbitrary**
) sub
GROUP BY 1, 2
ORDER BY 1, 2; -- optional
db<>fiddle here
Be aware that the arbitrary pick in case of "not been home" can give varying results! You may want to define a stable (opportune) pick instead.
There may be faster query variants, depending on undisclosed details.
About DISTINCT ON
and performance:
- Select first row in each GROUP BY group?
About the sort order:
- SQL select query order by day and month
If there can be NULL values:
- Sort by column ASC, but NULL values first?
Related Topics
Cannot Drop Postgresql Role. Error: 'Cannot Be Dropped Because Some Objects Depend on It'
SQL - Counting Rows with Specific Value
Difference Between Fetch/For to Loop a Cursor in Pl/Sql
Understanding the Differences Between Cube and Rollup
Managing Hierarchies in SQL: Mptt/Nested Sets VS Adjacency Lists VS Storing Paths
Querying Where Condition to Character Length
Getting the Sum of a Datediff Result
Select 2 Columns in One and Combine Them
Is There an Agreed Ideal Schema for Tagging
Using Union and Count(*) Together in SQL Query
How to Get the 30 Days Before Date from Todays Date
How to Use a Case Statement in a SQL from Clause
Odata Case In-Sensitive Filtering in Web API
Delete Statement in SQL Is Very Slow