New answers tagged count
2
Sounds like a simple group by:
select tagname, count(*)
from alarmmaster
where tagname like '%Sensor%'
or groupname like '%Sensor%'
group by tagname;
0
This should be faster, yet:
SELECT status, count(*)
FROM (
SELECT DISTINCT ON (ticket_number) status
FROM test
ORDER BY ticket_number, datetime DESC
) sub
GROUP BY 1;
db<>fiddle here
Assuming datetime is defined NOT NULL.
See:
Select first row in each GROUP BY group?
If there are many rows per ticket, one of these techniques will be ...
1
Use
WITH cte AS ( SELECT DISTINCT
ticket_number,
FIRST_VALUE(status) OVER (PARTITION BY ticket_number
ORDER BY datetime DESC) last_status
FROM test )
SELECT last_status, COUNT(last_status)
FROM cte
GROUP BY last_status
fiddle
for to obtain needed ...
1
Looking at the original question and example. Replace the comma with and.
-2
my trick is, make temporary table with field category and units then u make schedule at night to counting it n save to your temporary table, then ur just read that table with just 12 row in it, very faster, but ur count H-1, u need liitle trick againt to add current day ...
1
This query will update the oldest records that exceed the count of 500, so you keep 500 newest records for orgid = 1
update A
set `delete` = true
where orgid = 1 AND id IN (
SELECT id FROM A where orgid = 1
order by createdate DESC
LIMIT 500,18446744073709551615);
Top 50 recent answers are included
