logo
down
shadow

Sum of multiple column in SQL Server


Sum of multiple column in SQL Server

By : Gildas
Date : November 22 2020, 10:56 AM
I wish did fix the issue.
code :
Select sum(taxsum) + avg(linetotal)
From yourtable


Share : facebook icon twitter icon
Multiple column indexes optimization for multiple column queries on SQL Server

Multiple column indexes optimization for multiple column queries on SQL Server


By : Abhijit S
Date : March 29 2020, 07:55 AM
will help you Second one is ALWAYS faster for this query - but you need to put the more selective one first (in the order of indexes) to benefit more. The only exception is if for performance reasons, SQL decides to use clustered index so ignores the non-clustered.
The combination of two values create a much more selective criteria. Also it helps with performance since there is no BOOKMARK LOOKUP required on a covering index.
sql server multiple column values in single column by building query

sql server multiple column values in single column by building query


By : user3186719
Date : March 29 2020, 07:55 AM
hope this fix your issue From what I understand you want to select all columns seprated by | and in single column. You need to convert all non VARCHAR values to the VARCHAR and then using + operator you can concat them in single column.For converting you can use the cast. More about cast here.. And you may want to use the COALESCE also because if the any column value is null then it will return the null.More info on coalesce.
code :
   SELECT COALESCE(table1.ACID1,'defaultvalue') + '|'
        + CAST(COALESCE(table1.CLR_BAL_AMT1,0) AS VARCHAR)
        +'|'+CAST(table1.DEPOSIT_DUEDATE1 AS VARCHAR) 
        +'|'+ table1.ACID2+'|'+CAT(table1.CLR_BAL_AMT2 AS VARCHAR)    
        +'|'+CAST(table1.DEPOSIT_DUEDATE2 AS VARCHAR) 
        +'|'+table1.ACID3+'|'+CAST(table1.CLR_BAL_AMT3 AS VARCHAR) 
        +'|'+CAST(table1.DEPOSIT_DUEDATE3 AS VARCHAR) 
        +'|'+table1.ACID4+'|'+CAST(table1.CLR_BAL_AMT4)
        +'|'+CAST(table1.DEPOSIT_DUEDATE4 AS VARCHAR) 
        +'|'+ table1.ACID5+'|'+CAST(table1.CLR_BAL_AMT5 AS VARCHAR) 
        +'|'+CAST(table1.DEPOSIT_DUEDATE5 AS VARCHAR) AS FinalSampleVal
   FROM 
       yourtable As table1
How to display row value as column value in SQL Server (only one column rows value should be displayed as multiple colum

How to display row value as column value in SQL Server (only one column rows value should be displayed as multiple colum


By : Rene De Valery
Date : March 29 2020, 07:55 AM
this one helps. Here is one approach using dynamic crosstab:
SQL Fiddle
code :
use tempdb;
CREATE TABLE yourtable(
    id          INT,
    pname       VARCHAR(20),
    childname   VARCHAR(20)
)
INSERT INTO yourtable VALUES
(1, 'Parent1', 'p1child1'), 
(1, 'Parent1', 'p1child2'), 
(1, 'Parent1', 'p1child3'), 
(2, 'Parent2', 'p2child1'), 
(2, 'Parent2', 'p2child2'), 
(3, 'Parent3', 'p3child1'), 
(3, 'Parent3', 'p3child2'), 
(3, 'Parent3', 'p3child3'), 
(3, 'Parent3', 'p3child4'), 
(4, 'Parent4', 'p4child1'), 
(4, 'Parent4', 'p4child2'), 
(4, 'Parent4', 'p4child3');
DECLARE @maxNoChildren INT
DECLARE @sql1 VARCHAR(4000) = ''
DECLARE @sql2 VARCHAR(4000) = ''
DECLARE @sql3 VARCHAR(4000) = ''

SELECT TOP 1 @maxNoChildren = COUNT(*) FROM yourtable GROUP BY id ORDER BY COUNT(*) DESC

SELECT @sql1 = 
'SELECT
    id
    ,pname
'

SELECT @sql2 = @sql2 +
'   ,MAX(CASE WHEN RN = ' + CONVERT(VARCHAR(5), N) + ' THEN childname END) AS ' + QUOTENAME('child' + CONVERT(VARCHAR(5), N)) + CHAR(10)
FROM(
    SELECT TOP(@maxNoChildren)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM sys.columns a
    --CROSS JOIN sys.columns b
)T(N)
ORDER BY N

SELECT @sql3 =
'FROM(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY id ORDER BY (SELECT NULL))
    FROM yourtable
)t
GROUP BY id, pname
ORDER BY id'

PRINT(@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)
| id |   pname |   child1 |   child2 |   child3 |   child4 |
|----|---------|----------|----------|----------|----------|
|  1 | Parent1 | p1child1 | p1child2 | p1child3 |   (null) |
|  2 | Parent2 | p2child1 | p2child2 |   (null) |   (null) |
|  3 | Parent3 | p3child1 | p3child2 | p3child3 | p3child4 |
|  4 | Parent4 | p4child1 | p4child2 | p4child3 |   (null) |
How to make multiple aggregate column in pivot table using one column in SQL Server?

How to make multiple aggregate column in pivot table using one column in SQL Server?


By : rohitsingh
Date : March 29 2020, 07:55 AM
wish helps you I wants to show the cost and sales by year. , No need for the PIVOT. Just apply a conditional aggregation
code :
select 
    StoreID,
    Department.Name Department,
    Category.Name Category, 
    Sum(case when Year(Time)=2016 then ExtendedCost end) [Cost(2016)],
    Sum(case when Year(Time)=2017 then ExtendedCost end) [Cost(2017)],
    Sum(case when Year(Time)=2016 then ExtendedPrice end) [Sales(2016)],
    Sum(case when Year(Time)=2017 then ExtendedPrice end) [Sales(2017)],
from F_itemDailySalesParent
Inner join item with(Nolock) on item.id = F_itemDailySalesParent.ItemID
Left join Department with(Nolock) on Department.ID = item.DepartmentID
Left join Category with(Nolock) on Category.ID =item.CategoryID
where DATEPART(yyyy,Time) in (2016,2017)
group by StoreID,Department.Name,Category.Name
order by StoreID
Select *
 From (
        Select StoreID
              ,Department
              ,Category
              ,B.*
         From (
                select 
                    DATEPART(yyyy,Time) Years,
                    StoreID,
                    Department.Name Department,
                    Category.Name Category, 
                    Sum(ExtendedCost) Cost,
                    sum(ExtendedPrice) Sales
                from F_itemDailySalesParent
                Inner join item with(Nolock) on item.id = F_itemDailySalesParent.ItemID
                Left join Department with(Nolock) on Department.ID = item.DepartmentID
                Left join Category with(Nolock) on Category.ID =item.CategoryID
                where DATEPART(yyyy,Time) in (2016,2017)
                group by DATEPART(yyyy,Time),StoreID,Department.Name,Category.Name
              ) A
         Cross Apply ( values (concat('cost(',Years,')'),Cost)
                             ,(concat('sales(',Years,')'),Sales)
                     ) B (Item,Value)
      ) src
 Pivot (sum[Value]) For [Item] in ([cost(2016)],[cost(2017)],[sales(2016)],[sales(2017)] ) p
I receive multiple errors in SQL Server when trying to update a column in one table with a column for another

I receive multiple errors in SQL Server when trying to update a column in one table with a column for another


By : Edgard GraceFace
Date : March 29 2020, 07:55 AM
I hope this helps you . Updating with a join requires some weird syntax. You have to say update (no actual table name) before you've actually stated what that alias is actually tied to. Try this instead:
Related Posts Related Posts :
  • Trigger on extended properties? SQL SERVER
  • Database Schema guidance for an app store
  • sql trigger to update based on specific column is updated and if value is equal to 'x'
  • How to descope a local variable in t-sql
  • SQL Server Compact stored procedure with multiple values in 1 input
  • How to validate the ICCID number of a SIM card in TSQL?
  • how to calculate all previous datas sum of a sql column on a date range report
  • How to create view listing all friendship for "friend1,friend2" table in T-SQL and prevent inserting duplicate
  • Check month and day lies between two dates
  • How would I configure Effort Testing Tool to mock Entity Framework's DbContext withOut the actual SQL Server Database up
  • While looping through a function, Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (li
  • Collapsing records with adjacent start and end dates
  • SQL Server Management Studio 2008 - Timeout Expired error
  • Insert date from VB to SQL Server
  • How to find what table foreign key is from?
  • Sql Server Nested Inserts possible
  • Deterministic way of getting WeekEnding (Sun) for given date column (for use in computed column)
  • SQL Server, why does one order by query work with a string, but not with an integer?
  • How do I install a SQL Server on the hosted build agent in Visual Studio Online?
  • EXECUTE AS/REVERT within a transaction in SQL Server
  • Distinct 2 field AS one column SQL SERVER
  • how to create a new database after a trigger fired from a table
  • How to sort string alphabetically
  • Checking data in one column in a temp table to a column in another table
  • What's wrong with my IF/ELSE? "ELSE: Incorrect syntax near 'ELSE'."
  • CASE CLAUSE IN WHERE CONDITION
  • TSQL - Creating a running total of a column
  • Why am I getting not null here?
  • Why does sp_refreshview raise an exception on views that are not schema bound?
  • EXEC parameters from a SELECT
  • SQL Server Compact connection
  • Star Schema Structure - To many Dimensions
  • sort utf string in sql server with join clause
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org