SQL Count Overflow

SQL COUNT overflow

Use COUNT_BIG

SELECT COUNT_BIG(*) FROM Similarities WHERE T1Similarity = 0 OR T2Similarity = 0

SQL Count Calculation

This is two steps:

  1. Get the number of unique shops per client.
  2. 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 labels 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



Leave a reply



Submit