May 15, 2009 at 9:33 am
I have a table with several fields in it
product,description,analysisA,quantity,cost
I have 1000 records in the table
AnalysisA is either blank, AAA,BBB or CCC
100 records have analysisA as 'AAA, 200 as 'BBB' and 100 as 'CCC' the remaining 600 have it as blank
I want to select as follows
I require all 600 records where analysisA is blank and then group the remaining records based on this field.
I will therefore end up with 603 records with 600 records as is and 3 records(1 for AAA, BBB & CCC) with the quantity and cost totalled
Does this make sense?
I am having difficulty getting my head around it so any help is appreciated.
Jon
May 15, 2009 at 9:35 am
Sounds fun, can you post the DDL of the table and some sample data, I'm up for it, and I bet a lot of people will post back if you post some sample data along with DDL, 😉
Cheers,
J-F
May 15, 2009 at 9:47 am
Here, I built it for you, I hope that helps, tell me if I understood your problem correctly.
USE tempdb
DROP TABLE #Product
CREATE TABLE #Product (
ProductID INT IDENTITY ( 1 , 1 ),
DESCRIPTION VARCHAR(50),
AnalysisA VARCHAR(50),
quantity INT,
COST MONEY)
INSERT INTO #Product
SELECT 'AAA',
'AAA',
10,
30.50
UNION ALL
SELECT 'AAAA',
'AAA',
10,
50
UNION ALL
SELECT 'AAAAA',
'AAA',
10,
20
UNION ALL
SELECT 'BBB',
'BBB',
1,
50
UNION ALL
SELECT 'CCC',
'CCC',
5,
100
UNION ALL
SELECT 'DDD',
'',
1,
10
UNION ALL
SELECT 'EEE',
'',
2,
20
UNION ALL
SELECT 'FFF',
'',
3,
30
UNION ALL
SELECT 'GGG',
'',
4,
40
UNION ALL
SELECT 'HHH',
'',
5,
50
UNION ALL
SELECT 'III',
'',
6,
60
UNION ALL
SELECT 'JJJ',
'',
7,
70
SELECT AnalysisA,
SummedCost,
TotalAnalysed
FROM (SELECT AnalysisA,
sum(COST) AS SummedCost,
cOUNT(* ) AS TotalAnalysed
FROM #Product
WHERE AnalysisA ''
GROUP BY AnalysisA
UNION ALL
SELECT AnalysisA,
COST AS SummedCost,
1 AS TotalAnalysed
FROM #Product
WHERE AnalysisA = '') AS Analysed
order by AnalysisA
Cheers,
J-F
May 15, 2009 at 9:50 am
You can use a COMPUTE BY to generate subtotals for each subgroup
May 15, 2009 at 9:59 am
The solution looks fine, I just need the product and description to show as well and I think we are there.
May 15, 2009 at 10:14 am
After making a slight change everything is brilliant
Thanks for your help
Have a good weekend
May 15, 2009 at 10:15 am
Another way, should perform a little better.
SELECT AnalysisA, sum(COST) AS SummedCost, cOUNT(* ) AS TotalAnalysed
FROM #Product
GROUP BY ( CASE WHEN AnalysisA = '' THEN CONVERT( VARCHAR(40), NEWID() ) ELSE AnalysisA END ), AnalysisA
ORDER BY AnalysisA, SummedCost
--Ramesh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply