0

I have this data in my database which shows how many users have been invited and participated, and I want to calculate the following:

1- Total invitations (sum of count).
2- Participated (sum of allowed, to_be_paid, already_paid).
3- Responded (sum of allowed, to_be_paid, already_paid, user_declined, user_willing).
4- No response (sum of expired, offered).
5- Response rate (responded/ total*100).
6- Participation rate (participated / total*100).

Table view

I want to be able to get result like this:

Final result

Mohammed Al Deek
  • 13
  • 4
  • have you already tried anything? – Ryan Gadsdon Oct 22 '17 at 09:38
  • Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer –  Oct 22 '17 at 12:40
  • 1
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "_SQL_" is just a query language, not the name of a specific database product. –  Oct 22 '17 at 12:40
  • Please add your existing query to the question, do not attempt to use a comment (this s a comment). In future consider using text tables not images. – Paul Maxwell Oct 29 '17 at 08:57
  • This question is tagged metabase but does not seem to be about [metabase](https://www.metabase.com/). – bwdm Apr 23 '21 at 18:16

1 Answers1

0

I think this can help you.I changed to this and tested it was right

  declare @Responded int
  declare @Total int
  declare @Participated int

  set @Total=(select SUM([count]) as Total from [dbo].[tb1])

  set @Participated=(select SUM([count]) as Participated  from [dbo].[tb1]
  where Status in('allowed', 'to_be_paid', 'already_paid'))

  set @Responded=( select SUM([count]) as Responded   from [dbo].[tb1]
  where Status in('allowed', 'to_be_paid', 'already_paid', 'user_declined', 'user_willing'))
(SELECT  [Status]
      ,cast([count] as DECIMAL(38,0))
  FROM [Identity].[dbo].[tb1]
  )
  union 
  (
  select 'Total',cast(@Total as DECIMAL(38,0))
  )
  union 
  (
  select 'Participated',cast(@Participated as DECIMAL(38,0))
  )
  union 
  (
  select 'Responded',cast(@Responded as DECIMAL(38,0))
  )
  union 
  (
  select 'No response',cast(SUM([count]) as DECIMAL(38,0)) as [count] from [dbo].[tb1]
  where Status in('expired', 'offered')
  )
  union(
  select 'Response rate', cast(((100.0 * @Responded)/ @Total)as DECIMAL(38,0)) as [count]
  )
  union(
  select 'Participation rate',cast(((100.0 * @Participated)/ @Total)as DECIMAL(38,0))as [count]
  )

Result

Amirhossein Yari
  • 2,054
  • 3
  • 26
  • 38
  • I've tried this, but it didn't work for me :(. This is how my current query looks like: SELECT count(*) AS "count", "public"."participants"."status" AS "status". FROM "public"."participants". GROUP BY "public"."participants"."status". ORDER BY "public"."participants"."status" ASC. – Mohammed Al Deek Oct 22 '17 at 16:06
  • At first change your table name and column name if is incorrect – Amirhossein Yari Oct 23 '17 at 06:43