SQL query records showing in table

Hi,

I have one table called Test2. Please find the below screenshot.

In this table, one column called Status. It will contain possible values(1 or 2).

Status    1 means  success

Status     2 means Failure

 In the front end,  I have to show batch_fk and success and failure count in frond end table.

So, I have written a SQL query to fetch the success and failure count from status column of Test2 table

SQL1:

select {Test2}.[batch_fk],count(*) from {Test2} where {Test2}.[status]=1 group by {Test2}.[batch_fk];

This above query is for taking success count

Likewise, I have written a SQL query to fetch the error count as well. If the status is 2 means error

SQL2:

select {Test2}.[batch_fk],count(*) from {Test2} where {Test2}.[status]=2 group by {Test2}.[batch_fk];

In the frond end, I am able to show the batch_fk and success count. But I am not able to show the error count for each batch_fk since the query is different for error.

I have given the source for the table is SQL1.List.

How can I show the error count also in the table?

Regards,

Divya

Hi Divya,

Do you have a specific reason for separating those SQL queries? Because you can group by status also like following;

select {Test2}.[batch_fk],{Test2}.[status],count(*) from {Test2} 
group by {Test2}.[batch_fk], {Test2}.[status];

Hope it helps,

With best regards,
F.Karatay

Ferhat Karatay wrote:

Hi Ferhat,

Thanks for your reply. I want to display the records of SQL query in a table format in front end. Please find the below screenshot. In this format, I have to show the success count and error count for each batch_fk. As per your statement, I can do but there are two columns(Success and error) in a web screen(front end table). But query returns count which contain both success and error count. Using this count, how can I show both success and error count for each batch_fk?

Regards,

Divya

You could try a SQL query like this:

SELECT {Test2}.[batch_fk]
, COUNT(NULLIF(2, {Test2}.[status])) AS succesCount
, COUNT(NULLIF(1, {Test2}.[status])) AS errorCount
FROM {Test2}
GROUP BY {Test2}.[batch_fk];

This query takes advantage of the fact that the COUNT function does not count NULL values. The function NULLIF returns NULL if the two values are the same and the first value if they are not.

Timothy de Vries wrote:

For many reasons, a better option would be to make use of the standard aggregate within outsystems instead of using a custom SQL query. Using the standard aggregate, you will have to do this:

1. Create an aggregate with Test2 as a source (drag the test2 table into an action)

2. Click the "New attribute" button, rename the calculated attribute to "Succes". Then change the formula to "Test2.status = 1" (open the editor by double clicking the fx icon or the blue icon on the top right and selecting "Edit formula")

3. Do the same as 2, but now name it Error and use the formula "Test2.status = 2"

4. Create a group by on the "batch_fk" field (press the blue icon on the top right of the column and click "Group by"

5. Create a counnt on the "Succes" attribute you created in step 2 (press the blue icon on the top right of the column and click "Count") and name it SuccesCount

6. Do the same as 5, but now for the "Error" attribute and name it ErrorCount

Picture of the end result:

divya v wrote:

Hi Divya,

As I understand, you'd like to create a PIVOT table. I suggest you make a search about PIVOT in SQL.
Here is an example;

SyntaxEditor Code Snippet

SELECT * FROM (SELECT {Records}.[BatchFK], {Records}.[Status] FROM {Records}) SRC
PIVOT (count(*) FOR Status in ('Success' AS Success, 'Failure' as Fail)) PIV ORDER BY 1 ASC

Hope it helps.

With best regards,
F.Karatay

Timothy de Vries wrote:

Hi Timothy,

Your query is working properly. Thanks a lot. But, Now I should use aggregate instead of SQL query because of some reason. So I created aggregate where I added a new attribute called success count and gave this formula NULLIF(2, {Test2}.[status]). Unfortunately, it is showing the error saying that NULLIF is not a function.

Could you please help me.

Regards,

Divya

You can't use SQL formula's in an aggregate for as far as I know. 

Timothy already gave an extensive example for creating the aggregate, that should help you I think.

Timothy de Vries wrote:

Hi,

As you said, I have tried this also. But here what is happening is, If this condition satisfies(Test2.Status = 1), it returns true otherwise false. So, count function counts the records even both true and false.

let's assume Success(Test2.Status=1)

true

false

false

false

Error(Test2.Status=2)

false

true

true

true

So, now both success and error count is 4. But, this is not the case.

Bou actual count is 

success count  1

error count  3

Regards,

Divya

Hi Divya,

Use the same approach, but instead of TRUE / FALSE, put 1 / 0, and instead of count, use SUM.

Cheers.

Eduardo Jauch wrote:

Hi Eduardo,

I am not manually adding this value(true and false). If this condition(Test2.Status=1) satisfies, it returns true otherwise false.

Regards,

Divya

Hi Divya,

What Eduardo suggests is not to manually set 1 or 0, but with a formula. You need to change the values of the calculated fields from 'Test2.Status=1' to 'If(Test2.Status = 1, 1, 0)' for the Success column and 'Test.Status=2' to 'If(Test2.Status = 2, 1, 0)' for the Fail-column.

As he also stated, you need to change the count to a SUM.

Regards,
Lennart

Thanks @Lennart. That's exactly what I meant :)