Hello,
DB2 LUW V8 FixPack 13.
create table Table (ID varchar(20), USED char)
I need to find out the total row count per ID, as well as the row count
where USED=Y.
I could do this with a simple:
select X.ID, X.Total, Y.Total from
(select ID, COUNT(*) as Total from Table group by ID) as X
(select ID, COUNT(*) as Used_Total from Table where USED='Y' group by
ID) as Y
where X.ID=Y.ID
Depending on my table size, this query can be resource intensive and
take some time to finish.
Is there any other way to achieve the same result with an optimized
statement?
Thanks in advance, Michel 4 5605
Michel Esber wrote:
Hello,
DB2 LUW V8 FixPack 13.
create table Table (ID varchar(20), USED char)
I need to find out the total row count per ID, as well as the row count
where USED=Y.
I could do this with a simple:
select X.ID, X.Total, Y.Total from
(select ID, COUNT(*) as Total from Table group by ID) as X
(select ID, COUNT(*) as Used_Total from Table where USED='Y' group by
ID) as Y
where X.ID=Y.ID
Depending on my table size, this query can be resource intensive and
take some time to finish.
Is there any other way to achieve the same result with an optimized
statement?
Thanks in advance, Michel
Are you trying to find the ID's for which the exists a USED <'Y'?
SELECT DISTINCT ID FROM TABLE WHERE USED <'Y'
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference http://www.iiug.org/waiug/present/Fo...Forum2006.html
Michel Esber wrote:
Hello,
DB2 LUW V8 FixPack 13.
create table Table (ID varchar(20), USED char)
I need to find out the total row count per ID, as well as the row count
where USED=Y.
I could do this with a simple:
select X.ID, X.Total, Y.Total from
(select ID, COUNT(*) as Total from Table group by ID) as X
(select ID, COUNT(*) as Used_Total from Table where USED='Y' group by
ID) as Y
where X.ID=Y.ID
Depending on my table size, this query can be resource intensive and
take some time to finish.
Is there any other way to achieve the same result with an optimized
statement?
SELECT id, COUNT(*) AS total,
SUM(CASE WHEN used = 'Y' THEN 1 ELSE 0 END) AS used_total
FROM table
GROUP BY id
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Michel Esber wrote:
>
Hello,
DB2 LUW V8 FixPack 13.
create table Table (ID varchar(20), USED char)
I need to find out the total row count per ID, as well as the row count
where USED=Y.
I could do this with a simple:
select X.ID, X.Total, Y.Total from
(select ID, COUNT(*) as Total from Table group by ID) as X
(select ID, COUNT(*) as Used_Total from Table where USED='Y' group by
ID) as Y
where X.ID=Y.ID
Depending on my table size, this query can be resource intensive and
take some time to finish.
Is there any other way to achieve the same result with an optimized
statement?
SELECT id, COUNT(*) AS total,
SUM(CASE WHEN used = 'Y' THEN 1 ELSE 0 END) AS used_total
FROM table
GROUP BY id
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Knut and Serge, thanks. My access plan is now much more efficient with
a lower cost.
-M
This will also work.
SELECT id, COUNT(*) AS total,
COUNT(CASE WHEN used = 'Y' THEN 1 END) AS used_total
FROM table
GROUP BY id This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Igor Kryltsov |
last post by:
Hi,I am using slightly modified example posted by Doug Younger and answered
by Tom Lane
:)(http://archives.postgresql.org/pgsql-sql/1999-08/msg00159.php)
I have the following 2 tables:
Table...
|
by: darkdirk1 |
last post by:
SQL Value:
"COUNT(tbl__UserLog._UserLog_ID) AS Count_Logins "
Returns as varchar which is killing me, because my user interaction is loaded w/ sort features. Unfortunately the count_logins var...
|
by: sql_er |
last post by:
Hi all,
I am trying to convert an SQL statement into an XPath (or a sequence of
XPath) statements. More specifically, I have the following:
SELECT a
FROM b
WHERE c IN (SELECT d FROM e)
I...
|
by: AtCor |
last post by:
I am trying to filter data using count. For a given day and patient, I would like to return all the valid tests. When I use a count this way, it only returns the patients with at least 4 tests. I...
|
by: ntocher |
last post by:
Is it possible in a single query to search up to 12 tables for a similar text string and count the number each time a duplicate is found?
I have a table for each month with a string field that...
|
by: Bam |
last post by:
hey gang.
i am trying to pull a count from the top 16 records in a db.
select count(top 16 (username)) as cnt2 from bracketdb_"&tourney_id&" where
username <'BYE' order by POS
this is what...
|
by: cmartin1986 |
last post by:
I have written a sql query and I need it to return 0 when it doesn't find any matches to my criteria. I have tried adding iif statements, tried sum, and just Count, all of these methods work fine to...
|
by: Hasse1982 |
last post by:
Hi
I have a table KDOCUMENT with the columns
,
,
,
,
,
,
|
by: billelev |
last post by:
I have a cross tab query with Date as a row heading and a series of Names as column headings. The Value for each Date/Name intersection can either be -1, 0 or 1.
For example:
Date, Name1,...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |