Query to get Count

Moderator: NorbertKrupa

Post Reply
sach
Newbie
Newbie
Posts: 3
Joined: Sun Jul 01, 2012 5:31 am

Query to get Count

Post by sach » Mon Jul 02, 2012 7:20 am

Hi,

I have table

DESC SUCC_COUNT FAIL_COUNT
==================================
ABC 5 1
BCD 7 2

Here my intension is to find the sum of SUCC_COUNT and FAIL_COUNT for list of DESC. I have a possible list of values for DESC . DESC values should be among (ABC,BCD,CDE,XYZ). Here there are cases where some of the DESC would be present in table.

The query which I have
SELECT DESC, SUM(SUCC_COUNT,FAIL_COUNT) FROM TABLEA WHERE DESC in ('ABC','BCD','CDE',XYZ');

But this would return

ABC 6
BCD 9.

Am looking for a query which should return me

ABC 6
BCD 9
CDE 0
XYZ 0.

What way should I modify the script?.

Thanks
Sach

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Query to get Count

Post by JimKnicely » Mon Jul 02, 2012 3:21 pm

You are either going to have to insert two records into the tablea table:

Code: Select all

INSERT INTO tablea VALUES ('cde', 0, 0);
INSERT INTO tablea VALUES ('xyz', 0, 0);
Then write a query like this:

Code: Select all

dbadmin=> SELECT desc1, SUM(succ_count+fail_count) from tablea group by desc1;
 desc1 | SUM
-------+-----
 abc   |   6
 bcd   |   9
 cde   |   0
 xyz   |   0
(4 rows)
Or you can have a separate look up table that has all possible values for your desc column and then outer join to it:

Example:
I created a table named desc1 and it has these values:

Code: Select all

dbadmin=> select * from desc1;
 desc1
-------
 abc
 bcd
 cde
 xyz
(4 rows)
Here are the values in tablea:

Code: Select all

dbadmin=> select * from tablea;
 desc1 | succ_count | fail_count
-------+------------+------------
 abc   |          5 |          1
 bcd   |          7 |          2
(2 rows)
Now I can write a query like this:

Code: Select all

dbadmin=> select desc1.desc1, nvl(sum(succ_count+fail_count), 0) from tablea right join desc1 on tablea.desc1 = desc1.desc1 where desc1.desc1 in ('abc', 'bcd', 'cde', 'xyz') group by desc1.desc1;
 desc1 | nvl
-------+-----
 abc   |   6
 bcd   |   9
 cde   |   0
 xyz   |   0
(4 rows)
Hope this helps :)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”