September 6, 2015 at 3:31 pm
Hello. I put on the site excell file where I explained what I want to get . It is easier to explain through an example .
THIS IS MY INPUT DATA:
ACCOUNTS CODE ACCOUNTS NAME TOTAL
0 CLASS 0
011 Synthetic group
011-1301 Some name 1 25 $
011-1401 Some name 2 20 $
022 Synthetic group
022-1011 Some name 1 101 $
022-1012 Some name 2 50 $
1 CLASS 1
100 Synthetic group
100-0001 Some name 1 10 $
100-0002 Some name 2 30 $
120 Synthetic group
120-2011 Some name 1 40 $
120-3344 Some name 2 10 $
2 CLASS 2
200 Synthetic group
200-5687 Some name 1 25 $
200-3689 Some name 2 10 $
THIS IS RESULT WHICH I WANT TO GET: sum by class and sum by Synthetic group
ACCOUNTS CODE ACCOUNTS NAME TOTAL
011-1301 Some name 1 25 $
011-1401 Some name 2 20 $
011 Synthetic group 45 $
022-1011 Some name 1 15 $
022-1012 Some name 2 60 $
022 Synthetic group 75 $
0 CLASS 0 120 $
100-0001 Some name 1 10 $
100-0002 Some name 2 50 $
100 Synthetic group 60 $
120-2011 Some name 1 40 $
120-3344 Some name 2 30 $
120 Synthetic group 70 $
1 CLASS 1 130 $
200-5687 Some name 1 25 $
200-3689 Some name 2 10 $
200 Synthetic group 35 $
2 CLASS 2 35 $
CREATE TABLE accounts
(AccountCode NVARCHAR(10), AccountName NVARCHAR(50), [Total] int)
;
INSERT INTO accounts
([AccountCode], AccountName, Total)
VALUES
('0', 'CLASS 0', 0),
('011', 'Synthetic group', 0),
('011-1301', 'Some name 1', 25),
('011-1401', 'Some name 2', 20 ),
('022', 'Synthetic group', 0),
('022-1011', 'Some name 1', 15),
('022-1012', 'Some name 2', 50),
('1', 'CLASS 1', 0),
('100', 'Synthetic group',0 ),
('100-0001', 'Some name 1', 10),
('100-0002', 'Some name 2', 30),
('120', 'Synthetic group', 0),
('120-2011', 'Some name 1', 40),
('120-3344', 'Some name 2', 10),
('2', 'CLASS 2', 0),
('200', 'Synthetic group',0 ),
('200-5687', 'Some name 1', 10),
('200-3689', 'Some name 2', 30)
;
September 6, 2015 at 3:41 pm
Please post create table statements and sample data in an easily usable form (insert statements). Personally I'm not going to download and open a strange excel file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2015 at 4:41 pm
I have edit my question
September 6, 2015 at 4:45 pm
Without a CREATE TABLE to easily test with (and it being around midnight), I suggest you read up on Grouping Sets, it should allow you to do what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2015 at 4:36 am
I have edit my question.
I looked grouping set and I wrote this..
with data as (
select ACCOUNTCODE, substring(ACCOUNTCODE, 1, len(ACCOUNTCODE) - 1) as Prefix, accountName, Total from accounts
)
select
case when grouping(accountName) = 1 then Prefix else min(ACCOUNTCODE) end as Code,
case when grouping(accountName) = 1 then '-' else accountName end as Name,
sum(Total) as Total
from data
group by grouping sets ( (Prefix, accountName), (Prefix) )
order by Prefix, grouping(accountName), Code
but that's not result which I wanted . I added a link where you can see my example: http://www.sqlfiddle.com/#!3/1b28d/1
September 7, 2015 at 5:36 am
September 7, 2015 at 5:54 am
Thanks for the sample data
This does what you asked.
CREATE TABLE #accounts (
AccountCode NVARCHAR(10),
AccountName NVARCHAR(50),
Total INT
);
INSERT INTO #accounts
(AccountCode, AccountName, Total)
VALUES ('0', 'CLASS 0', 0),
('011', 'Synthetic group', 0),
('011-1301', 'Some name 1', 25),
('011-1401', 'Some name 2', 20),
('022', 'Synthetic group', 0),
('022-1011', 'Some name 1', 15),
('022-1012', 'Some name 2', 50),
('1', 'CLASS 1', 0),
('100', 'Synthetic group', 0),
('100-0001', 'Some name 1', 10),
('100-0002', 'Some name 2', 30),
('120', 'Synthetic group', 0),
('120-2011', 'Some name 1', 40),
('120-3344', 'Some name 2', 10),
('2', 'CLASS 2', 0),
('200', 'Synthetic group', 0),
('200-5687', 'Some name 1', 10),
('200-3689', 'Some name 2', 30);
GO
WITH data
AS (SELECT AccountCode,
CASE WHEN AccountCode LIKE '%-%' THEN LEFT(AccountCode, CHARINDEX('-', AccountCode) - 1)
ELSE AccountCode
END AS SyntheticGroup,
LEFT(AccountCode, 1) AS Class,
AccountName,
Total
FROM #accounts
WHERE Total > 0
)
SELECT COALESCE(data.AccountCode, data.SyntheticGroup, data.Class) AS AccountCode,
CASE WHEN GROUPING(SyntheticGroup) = 0
AND GROUPING(AccountCode) = 0 THEN MIN(data.AccountName)
WHEN GROUPING(SyntheticGroup) = 0
AND GROUPING(AccountCode) = 1 THEN 'Synthetic Group'
WHEN GROUPING(SyntheticGroup) = 1
AND GROUPING(AccountCode) = 1 THEN 'CLASS ' + CAST(Class AS VARCHAR(4))
END AS Testing,
SUM(Total) AS Total
FROM data
GROUP BY GROUPING SETS((Class),
(Class, SyntheticGroup),
(Class, SyntheticGroup, AccountCode))
ORDER BY data.Class,
CASE WHEN GROUPING(SyntheticGroup) = 1 then '999' ELSE data.SyntheticGroup END,
CASE WHEN GROUPING(AccountCode) = 1 THEN '99999999' ELSE data.AccountCode
END
DROP TABLE #accounts
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2015 at 6:01 am
Jayanth_Kurup (9/7/2015)
you need to use Group by ROLLUP
Rollup generates a grand total row, which was not asked for. Easier to use Grouping Sets to just get the totals needed than to then have to filter out the grand total row
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2015 at 6:20 am
Yes this is good. But what if i have input data in first column without '-'. Like this
CREATE TABLE #accounts (
AccountCode NVARCHAR(10),
AccountName NVARCHAR(50),
Total INT
);
INSERT INTO #accounts
(AccountCode, AccountName, Total)
VALUES ('0', 'CLASS 0', 0),
('011', 'Synthetic group', 0),
('0111301', 'Some name 1', 25),
('0111401', 'Some name 2', 20),
('022', 'Synthetic group', 0),
('0221011', 'Some name 1', 15),
('0221012', 'Some name 2', 50),
('1', 'CLASS 1', 0),
('100', 'Synthetic group', 0),
('1000001', 'Some name 1', 10),
('1000002', 'Some name 2', 30),
('120', 'Synthetic group', 0),
('1202011', 'Some name 1', 40),
('1203344', 'Some name 2', 10),
('2', 'CLASS 2', 0),
('200', 'Synthetic group', 0),
('2005687', 'Some name 1', 10),
('2003689', 'Some name 2', 30);
GO
WITH data
AS (SELECT AccountCode,
CASE WHEN AccountCode LIKE '%-%' THEN LEFT(AccountCode, CHARINDEX('-', AccountCode) - 1)
ELSE AccountCode
END AS SyntheticGroup,
LEFT(AccountCode, 1) AS Class,
AccountName,
Total
FROM #accounts
WHERE Total > 0
)
SELECT COALESCE(data.AccountCode, data.SyntheticGroup, data.Class) AS AccountCode,
CASE WHEN GROUPING(SyntheticGroup) = 0
AND GROUPING(AccountCode) = 0 THEN MIN(data.AccountName)
WHEN GROUPING(SyntheticGroup) = 0
AND GROUPING(AccountCode) = 1 THEN 'Synthetic Group'
WHEN GROUPING(SyntheticGroup) = 1
AND GROUPING(AccountCode) = 1 THEN 'CLASS ' + CAST(Class AS VARCHAR(4))
END AS Testing,
SUM(Total) AS Total
FROM data
GROUP BY GROUPING SETS((Class),
(Class, SyntheticGroup),
(Class,
SyntheticGroup,
AccountCode))
ORDER BY data.Class,
ISNULL(data.SyntheticGroup, '999'),
CASE WHEN GROUPING(AccountCode) = 1 THEN '999999'
ELSE data.AccountCode
END
DROP TABLE #accounts
September 7, 2015 at 6:27 am
Then you should have mentioned that in your requirements. I can't read your mind.
Replace the LEFT with CharIndex with just a LEFT(3), and the CASE checking for LEN >=3, assuming that you haven't forgotten to mention the cases with 4 or 2 digit groups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2015 at 6:58 am
Thank you very much!
September 7, 2015 at 11:34 pm
GilaMonster (9/7/2015)
Jayanth_Kurup (9/7/2015)
you need to use Group by ROLLUPRollup generates a grand total row, which was not asked for. Easier to use Grouping Sets to just get the totals needed than to then have to filter out the grand total row
Yes , i guess Rollup is over Kill but I have included the code here anyway in case it helps others. I admit its not perfect 🙂
;with cte as (
select
cast(left(AccountCode ,1) as varchar(10)) as g1,
cast(left(AccountCode ,3) as varchar(10)) as g2,
cast(left(AccountCode ,8) as varchar(10)) as g3 , sum(total) as total
from #accounts
group by rollup ( cast(left(AccountCode ,1) as varchar(10)),
cast(left(AccountCode ,3) as varchar(10)),
cast(left(AccountCode ,8) as varchar(10)) )
)
select g1 , g2 , Coalesce(g3, g2 , g1) as AccountCode, total
from cte
where total > 0
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply