New answers tagged count
3
You are using integer division (more precisely bigint) which yields not fractional values. You need to cast one value to numeric in order to get what you want:
select (select COUNT(*)
FROM brd.gaf_info
where can_piecesre_sucbattery = 'pr'
and gaf_start_date > '2019-11-13 01:01:01.754'
)::numeric /
(select ...
1
Obviously, COUNT(DISTINCT) with multiple columns counts unique combinations of the specified columns' values. However, one other important point is that a tuple is counted only if none of the individual values in the tuple is null. If that last aspect of the behaviour is what you are trying to achieve, you could emulate it using a conditional inside COUNT. ...
0
select count(*) from (select distinct col1, col2 from demo) as distinctAll
0
The question "how many toys for the each kid" can be answered with a simple group by:
SELECT kid.id, kid.name, count(*)
FROM Kid
JOIN Toy ON Kid.id = toy.kidsid
group by kid.id, kid.name;
(Note that I also fixed your join condition as Kid.toyid = Kid.toyid is definitely wrong)
That will however not display kids that don't have any toys (as they are ...
1
You can use a compound statement like this
CREATE TABLE COUNT_ROWS (
TABSCHEMA VARCHAR(128) NOT NULL
, TABNAME VARCHAR(128) NOT NULL
, ROW_COUNT BIGINT
)
@
BEGIN
FOR C AS cur CURSOR WITH HOLD FOR
SELECT 'INSERT INTO COUNT_ROWS SELECT ''' || TABSCHEMA || ''',''' || TABNAME || ''', COUNT(*) FROM '
|| '"' || TABSCHEMA || '...
1
You need to do a loop over the tables. From a sh in Linux you can do something like:
db2 connect to db
for t in $(db2 -x "select rtrim(tabschema)||'.'||rtrim(tabname) from syscat,tables where tabschema = 'COM' order by tabname"); do
db2 "select '$t', count(1) from $t"
done
-x removes headers and other info.
1
WITH cte AS ( SELECT product_id
FROM j_product_store_availability
GROUP BY product_id
HAVING 0 = SUM(available_count)
AND 0 = SUM(onway) )
SELECT t.*
FROM j_product_store_availability t
JOIN cte ON t.product_id = cte.product_id
Of course the solution is correct only in the case when both ...
0
I don't get your example, but if that's just an illustration of the question in the heading, the answer is a window function:
SELECT /* all your columns and expressions */,
count(*) OVER () AS total_count
FROM /* the rest of your query */
This will append a column to each result row with the total number of result rows.
The window function is ...
1
You have to use a window function, which does something similar to a GROUP BY, but outpots all rows and not just the groups.
SELECT *
FROM (SELECT *,
sum(available_count) OVER (PARTITION BY product_id) AS sum_count
FROM j_product_store_availability psa) AS subq
WHERE onway = 0
AND sum_count = 0
ORDER BY product_id;
Top 50 recent answers are included
