SQL Script - Contents within the SQL table as header - Newbie

Other SQL Server Topics
1

Hello SQL world!

I am trying to write a script to get some contents out of a table, which hold information such as a count, status, names etc. Count will hold the value that I want to get out but I want to have one row with the count value but the get the headings from the STATUS column which has FAILED / NEW / REJECTED etc

An example on how I want to display it:

Failed New Rejected
0 2 4

I have attempted a few things but can't get the result I am looking for:

Select Count(*)
From  (
    Select STATUS
    From TARGET.CDC_EVENT_TRANSACTION
    Group By STATUS)
where STATUS in ('DONE','FAILED', 'REJECTED')
2

Welcoke @simpsa27

Look into pivot

3

@yosiasz

Thanks for the advice, I have tried the following but keep getting errors, any ideas on where I am going wrong. Keeps saying the first AS is an error "missing expression"

> select 
> count (*),
> NEW,
> DONE,
> PROCESSED,
> FAILED,
> REJECTED
> FROM
>   (select STATUS,count(*) 
>      from TARGET.CDC_EVENT_TRANSACTION
>   ) AS Svt
>   (
>   SUM (count (*))
>   FOR STATUS IN ([NEW],[DONE],[PROCESSED],[FAILED],[REJECTED])
>   ) AS Pvt
4

you missed Ending Close Bracket
syntax error

if that is your error
there will be million or billion kinds of errors to choose from
:rofl:

Select Count(*)
From  (
    Select STATUS
    From TARGET.CDC_EVENT_TRANSACTION
    Group By STATUS)
where STATUS in ('DONE','FAILED', 'REJECTED'))
5

you get the idea ? .. please let me know

    Select  
            sum(case when STATUS = 'Done' then 1 else 0 end) as 'Done'
        ,   sum(case when STATUS = 'French Fries' then 1 else 0 end) as 'French Fries'
    From TARGET.CDC_EVENT_TRANSACTION
     where STATUS in ('DONE','FAILED', 'REJECTED')
6

@harishgg1 I am getting same issue, the first 'AS' returns error "missing expression" as per my previous comment

7 
use sqlteam
go

declare @CDC_EVENT_TRANSACTION table(name varchar(50), 
status varchar(50))

--this is just sample data to test things out
insert into @CDC_EVENT_TRANSACTION
select top 1000 name, 
   case
     when column_id /2 =0 then 'DONE'
	 when column_id /3 =0 then 'FAILED'
	 when column_id * 2.5 = 2 then 'NEW'
	 else 'REJECTED' end as status    
  from sys.all_columns

  select * 
    from @CDC_EVENT_TRANSACTION
	pivot (
			count(name)
			FOR STATUS IN ([NEW],[DONE],
[PROCESSED],[FAILED],[REJECTED])
   ) AS Pvt

--test validity of pivot
select count(1), status
    from @CDC_EVENT_TRANSACTION
	group by  status
8

hope this helps

9

Decided against using a pivot

select sum(case when CDC_EVENT_TRANSACTION.STATUS = 'NEW' then 1 else 0 end ) as NEW_CNT, sum(case when CDC_EVENT_TRANSACTION.STATUS = 'DONE' then 1 else 0 end ) as DONE_CNT, sum(case when CDC_EVENT_TRANSACTION.STATUS = 'PROCESSING' then 1 else 0 end ) as PROCESS_CNT, sum(case when CDC_EVENT_TRANSACTION.STATUS = 'FAILED' then 1 else 0 end ) as FAIL_CNT ,sum(case when CDC_EVENT_TRANSACTION.STATUS = 'REJECTED' then 1 else 0 end ) as REJECT_CNT from TARGET.CDC_EVENT_TRANSACTION where CREATE_TIME > sysdate-100 and group by domain

Thanks for all your help guys :slight_smile:

10

This is probably for the best - PIVOT isn't as flexible. We refer to this method as a cross-tab and there are some great articles over at SQL Server Central:

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns – SQLServerCentral

11

Thanks for the information guys :slight_smile:

select sum(case when CDC_EVENT_TRANSACTION.STATUS = 'NEW' then 1 else 0 end ) as NEW_CNT, sum(case when CDC_EVENT_TRANSACTION.STATUS = 'DONE' then 1 else 0 end ) as DONE_CNT, sum(case when CDC_EVENT_TRANSACTION.STATUS = 'PROCESSING' then 1 else 0 end ) as PROCESS_CNT, sum(case when CDC_EVENT_TRANSACTION.STATUS = 'FAILED' then 1 else 0 end ) as FAIL_CNT ,sum(case when CDC_EVENT_TRANSACTION.STATUS = 'REJECTED' then 1 else 0 end ) as REJECT_CNT from TARGET.CDC_EVENT_TRANSACTION where CREATE_TIME > sysdate-15/24/60 and domain = 'customerRate'

The following returns as but want it to return as 0, I tried to use the COALESCE but kept coming back with errors, any ideas?

12

very hard to see what is happening on your computer. what error are you seeing

13

is this Oracle database?

14

Yes it’s oracle

15

still waiting for error

16

This would be the code I've used:

select COALESCE(sum(case when CDC_EVENT_TRANSACTION.STATUS = 'NEW' then 1 else 0 end )) as NEW_CNT, COALESCE(sum(case when CDC_EVENT_TRANSACTION.STATUS = 'DONE' then 1 else 0 end )) as DONE_CNT, COALESCE(sum(case when CDC_EVENT_TRANSACTION.STATUS = 'PROCESSING' then 1 else 0 end )) as PROCESS_CNT, COALESCE(sum(case when CDC_EVENT_TRANSACTION.STATUS = 'FAILED' then 1 else 0 end )) as FAIL_CNT ,COALESCE(sum(case when CDC_EVENT_TRANSACTION.STATUS = 'REJECTED' then 1 else 0 end )) as REJECT_CNT from TARGET.CDC_EVENT_TRANSACTION where CREATE_TIME > sysdate-15/24/60 and domain = 'customerRate'

Error is as follows

ORA-00938: not enough arguments for function
00938. 00000 - "not enough arguments for function"
Cause:
*Action:
Error at Line: 1 Column: 397

17