| View previous topic :: View next topic |
| Author |
Message |
moyeenkhan
Beginner
Joined: 04 Dec 2002
Posts: 64
Topics: 21
|
 Posted: Thu Jul 10, 2003 9:46 am Post subject: Count Of COlumns With Conditions |
 |
|
I have three columns A, B, C. A is the key. I would like to get a count of the Key A whenever it changes alongwith all counts of B and Counts of C Whenever it is blank associated with the Key. I would like to get it in a single row. Is it possible?
Thanks |
|
| Back to top |
|
 |
Glenn
Beginner
Joined: 23 Mar 2003
Posts: 56
Topics: 3
|
| Posted: Thu Jul 10, 2003 2:07 pm Post subject: |
|
|
| Please describe in detail what you are wanting. |
|
| Back to top |
|
|
moyeenkhan
Beginner
Joined: 04 Dec 2002
Posts: 64
Topics: 21
|
| Posted: Thu Jul 10, 2003 2:50 pm Post subject: |
|
|
An example should clarify
Col A COl B Col C\
A1 B1 C1
A1 B1 --
A1 B2 C3
A1 B4 --
A2 ......................
What I need to get is
A1 4 2
in a single row.
4 = Count Of Col B
2 = Count Of non-blank Col C |
|
| Back to top |
|
|
Himesh
CICS Forum Moderator
Joined: 20 Dec 2002
Posts: 80
Topics: 21
Location: Chicago
|
| Posted: Thu Jul 10, 2003 3:11 pm Post subject: |
|
|
Moyeenkhan,
The following SQL should give you the result.
| Code: |
SELECT
A.columnA,
B.ACountB,
C.ACountC
FROM
tableA A,
(SELECT
columnA,
COUNT(*) ACountB
FROM
tableA
group by
columnA
) B,
(SELECT
columnA,
count(*) ACountC
FROM
tableA
WHERE
columnC IS NOT NULL
group by
columnA
) C
WHERE
A.columnA = B.columnA
AND B.columnA = C.columnA
|
Please Note:
ACountB will give the total count of columnA, i.e. occurance of ColumnA
ACountC will give the total count of columnA where columnC is not NULL
Though, in your first post you wanted the count of column A with blank column C, and in your later post you wanted the count of column A with non-blank Column C.
So please change the SQL from "IS NOT NULL" to "IS NULL" if required.
regards,
Himesh |
|
| Back to top |
|
|
moyeenkhan
Beginner
Joined: 04 Dec 2002
Posts: 64
Topics: 21
|
| Posted: Thu Jul 10, 2003 5:59 pm Post subject: |
|
|
I found a solution.
SELECT
A
, COUNT(*)
, SUM(CASE WHEN C IS NULL THEN 0 ELSE 1 END)
FROM
TABLE
GROUP
BY A
Many thanks Himesh |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
