We’re rewarding the question askers & reputations are being recalculated! Read more.

New answers tagged

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