logo
down
shadow

Summing a Case Statement with a check for Value in Table Variable


Summing a Case Statement with a check for Value in Table Variable

By : Sheng Yuan
Date : October 18 2020, 01:08 AM
will help you I have a report that I am working on, the report has a few parameters passed to it. One of them is a string of UserIds that are operated by commas. These are split and sent into a table variable. In side of my query I need to be able to sum values. In a few of these Sums I need to by case add this value to the sum and in another case not add to it. The code looks something like this... , Perhaps you don't need the aggregation at all:
code :
SELECT TrnId, TrnCode,
       (CASE WHEN PolicyUseId in (select UseId From @Table) then TotalPremium else 0 end) as Value
       AnotherColumn
FROM SomeTableJoinedOnOtherThings;
SELECT TrnId, TrnCode,
       SUM(CASE WHEN tt.UseId IS NOT NULL THEN TotalPremium ELSE 0 END) as Value,
       AnotherColumn
FROM SomeTableJoinedOnOtherThings t LEFT JOIN
     @Table tt
     ON t.PolicyUseId = tt.UseId
GROUP BY TrnId, TrnCode, AnotherColumn;


Share : facebook icon twitter icon
Summing in a Case Statement

Summing in a Case Statement


By : iamgsguru
Date : March 29 2020, 07:55 AM
should help you out As said remove the quotes, & you can't have a column alias beginning with a digit; escape it:
code :
...ELSE 0 END) AS [2010_Hours]
SQL Case When statement to check if field is in temp table

SQL Case When statement to check if field is in temp table


By : A. DARDORY
Date : March 29 2020, 07:55 AM
wish helps you Apologies in advance if something like this has already come up, but I can't find it.
code :
select (case when xsup_ID in (select id from @x) then 'YES' else 'NO' end) as Ins
Summing outputs from a case statement

Summing outputs from a case statement


By : user2339269
Date : March 29 2020, 07:55 AM
this one helps. I have a query like this: , I do believe you are after:
code :
SELECT
SUM(CASE WHEN [Group] = 1 then 1 ELSE 0 END ) AS InCount,
SUM(CASE WHEN [Group] = 0 then 1 ELSE 0 END ) AS OutCount
 FROM [GW_Test_Back_Up].[dbo].[ARC_Calls_ReportView]
SELECT 'InCount' AS Type,
SUM(CASE WHEN [Group] = 1 then 1 ELSE 0 END ) AS InCount
FROM [GW_Test_Back_Up].[dbo].[ARC_Calls_ReportView]
UNION ALL
SELECT 'OutCount' AS Type,
SUM(CASE WHEN [Group] = 0 then 1 ELSE 0 END ) AS OutCount
FROM [GW_Test_Back_Up].[dbo].[ARC_Calls_ReportView]
SELECT 
CASE WHEN m.InCount > 10 THEN 'High' ELSE 'Low' END AS InCountStatus
CASE WHEN m.OutCount > 10 THEN 'High' ELSE 'Low' END AS OutCountStatus
FROM 
(
   SELECT
     SUM(CASE WHEN [Group] = 1 then 1 ELSE 0 END ) AS InCount,
     SUM(CASE WHEN [Group] = 0 then 1 ELSE 0 END ) AS OutCount
   FROM [GW_Test_Back_Up].[dbo].[ARC_Calls_ReportView]
) m
Case statement that has to check through another table

Case statement that has to check through another table


By : Zain Bin Tariq
Date : March 29 2020, 07:55 AM
I hope this helps . Hive doesn't support subqueries in the SELECT clause. But, you can do this using joins:
code :
select t1.*,
       (case when t2a.ind_A = 1 or t2b.ind_A = 1 then 1 else 0 end) as ind_A,
       (case when t2a.ind_B = 1 or t2b.ind_B = 1 then 1 else 0 end) as ind_B,
       (case when t2a.ind_C = 1 or t2b.ind_C = 1 then 1 else 0 end) as ind_C
from t1 left join
     t2 t2a
     on t1.code1 = t2a.code left join
     t2 t2b
     on t1.code2 = t2b.code;
How to check varchar variable if its Numeric or not in CASE When statement

How to check varchar variable if its Numeric or not in CASE When statement


By : Arnas R
Date : March 29 2020, 07:55 AM
it should still fix some issue I have complex query where i am having a varchar variable. I need to perform various operation on it based on the datatype of data i receive in that variable. , This seems to work perfectly with sql_variant
code :
select CASE WHEN IsNUMERIC(@testVal)=1 THEN Cast(@testVal as numeric) ELSE Cast(@testVal as sql_variant) End
Related Posts Related Posts :
  • Postgres GROUP BY looking at dates ranges
  • SQL query combine select statements
  • INFORMIX: UPDATE with ALIAS?
  • How to display a number with a space between currency and the number
  • Get last 2 years value and oldest year SQL
  • Calculate contribution
  • SQL Server: force cleanup of deallocated internal objects in tempdb to release disk space reserved for them within an op
  • sql oracle in() clause
  • How to trim out letter in the column
  • Update field in one table based on select in another one
  • SQL Server : Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
  • How to calculate the RANK from another column than the Window order?
  • Add a constraint to all columns in all tables
  • Simplify SQL code
  • Oracle sql - sorting record with selected records on top
  • SQL Server 2016: Numbers Wildcard [^0-9] - filter out 0000
  • What is the most elegant way to store timestamp with nanosec in postgresql?
  • CASE WHEN SQL Syntax Error
  • Performance Issue - Select firstdate per month on a very big table
  • How to Group by with sum of multi row in another table
  • using exists clause in a sum case (oracle)
  • SQL: How to count number of each vowel separately in a string
  • How to sort data in stored procedure with column positions of select statement with CASE
  • regexp_substr: find the text before a given patern
  • How to create a view that can identify each person's club out of many club?
  • Create New Field QGIS Depending on Another Field Using Case
  • How can you find the beginning of a week for a given date in Netezza?
  • Automatically assigning a primary key to an entry in MS-Access via Netbeans
  • Convert OUTER APPLY to LEFT JOIN
  • I need DateTime in SELECT, but if in GROUP BY provides unusable report
  • Concatenate with string but exclude when null
  • Copy null data to not null in a column
  • ORA-01861: literal does not match format string 01861
  • Postgres query for calendar
  • Error in Join query with pagination for Oracle 10g
  • Replace %20 string in URL with Underscore using SQL Server Database Table
  • SQL how to replace multiple characters in a single field of the same table with single query
  • Why does this Postgres Select Query work in the local Docker Container but not on Amazon RDS Postgres?
  • Combine two Oracle SQL queries with one LEFT OUTER JOIN and two INNER JOINS from four tables
  • How to generate unique id based on start date and end date for the next 40 years in SQL table
  • Single SELECT Statement to Retrieve All Possible Combinations of 1 Columns Values Oracle
  • Stored procedure fails, but works correctly when it runs manually
  • T-SQL Null comparisons in stored procedure
  • SQL query returning error only when CASE WHEN is used
  • Last value based on criteria
  • SELECT assigns a value to local variable and refers it again. Are the results guaranteed by SQL Server?
  • Do 'set operations' have an prescribed order of execution, or do they execute in order of evaluation?
  • SQL Select with distinct last 10 characters
  • Delete duplicate records based on timestamps
  • PostgreSQL Find all matches for given player
  • oracle regular expression issue
  • MS Access VBA query multiple tables
  • SQL update table with distinct
  • selecting data for the first 12 months
  • How to convert Big Int into Datetime style 109
  • SELECT the record (time) of a specific change for one ID
  • Should I put a row number filter in join condition or in a prior CTE?
  • Nest a select statement inside array_to_json(array_agg(row_to_json())) in PostgreSQL
  • Unable to pull Custom Dimension for a specific ID using BigQuery Standard SQL
  • Count of id per day using window function
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com