Need help Totaling rows and columns
-
I have the following SQL statement which displays the count of the specified model for each location. THe output is as follows:
Location Model#1 Model#2 Model#3 Model#4
Location#1 Qty QTY QTY QTY
Location#2 QTY QTY QTY QTY
SELECT DISTINCT
s.Location00 AS 'SSA Location',
SUM(CASE c.Model0
WHEN 'OptiPlex GX240' THEN 1
ELSE 0
END) AS 'OptiPlex GX240',
SUM(CASE c.Model0
WHEN 'OptiPlex GX260' THEN 1
ELSE 0
END) AS 'OptiPlex GX260',
SUM(CASE c.Model0
WHEN 'Latitude C840' THEN 1
ELSE 0
END) AS 'Latitude C840',
SUM(CASE c.Model0
WHEN 'OptiPlex GX400' THEN 1
ELSE 0
END) as 'OptiPlex GX400'
FROM v_SSA_INFO s,
v_GS_COMPUTER_SYSTEM c
WHERE c.ResourceID = s.MachineID
GROUP BY s.Location00
GROUP BY s.Location00
I need to total the rows and the columns so that I get the following output:
Location Model#1 Model#2 Model#3 Model#4 Total
Location#1 Qty QTY QTY QTY TOTAL1
Location#2 QTY QTY QTY QTY TOTAL2
Total TOTAL1 TOTAL2 TOTAL3 TOTAL4 TOTAL
I can't figure out how to get the totals, can anyone give me a hand wih this? THanks!
-
Have you looked at WITH ROLLUP? That may help..
_______________
Convert DTS to SSIS | Database Documentation | SSIS Performance | SSIS Monitoring -
If you Select the above statement into a temp table, you can then use the following Statement on the temptable (changing the names respectively of course!) - I named my temptable #qty :
SELECT *
FROM
(SELECT q.location, q.model1, q.model2, q.model3,q.model4, t.overallTotal
FROM #qty q
inner Join
(SELECT SUM(model1 + model2 + model3 + model4)as overallTotal, location
FROM #qty
GROUP BY location) t
ON q.location = t.location) p1
UNION
SELECT 'Total Models', SUM(model1), SUM(model2), SUM(model3), SUM(model4), SUM(overallTotal)
FROM
(SELECT q.location, q.model1, q.model2, q.model3,q.model4, t.overallTotal
FROM #qty q
inner Join
(SELECT SUM(model1 + model2 + model3 + model4)as OverallTotal, location
FROM #qty
GROUP BY location) t
ON q.location = t.location) p2
I dont know how effective this is, but it works
Greetings from Germany
~nano
-
Try this:
CREATE TABLE #SSA ([MachineID] [int] IDENTITY (1, 1) NOT NULL ,
[Location00] [varchar] (30) NOT NULL ,
CONSTRAINT [PK_SSA] PRIMARY KEY CLUSTERED ([MachineID]) ON [PRIMARY]
 
ON [PRIMARY]CREATE TABLE #GS_ComputerSys ([UID] [int] IDENTITY (1, 1) NOT NULL ,
[ResourceID] [Int] NOT NULL,
[Model0] [VarChar] (50) NOT NULL,
[Qty] [Int] NOT NULL Default 0
CONSTRAINT [PK_GS_ComputerSys] PRIMARY KEY CLUSTERED ([UID]) ON [PRIMARY]
 
ON [PRIMARY]INSERT INTO #SSA
VALUES('Location 1')
INSERT INTO #SSA
VALUES('Location 2')
INSERT INTO #SSA
VALUES('Location 3')
-- Location 1
INSERT INTO #GS_ComputerSys
VALUES(1, 'OptiPlex GX240', 5)
INSERT INTO #GS_ComputerSys
VALUES(1, 'OptiPlex GX260', 1)
INSERT INTO #GS_ComputerSys
VALUES(1, 'Latitude C840', 25)
INSERT INTO #GS_ComputerSys
VALUES(1, 'OptiPlex GX400', 30)
-- Location 2
INSERT INTO #GS_ComputerSys
VALUES(2, 'OptiPlex GX240', 0)
INSERT INTO #GS_ComputerSys
VALUES(2, 'OptiPlex GX260', 47)
INSERT INTO #GS_ComputerSys
VALUES(2, 'Latitude C840', 15)
INSERT INTO #GS_ComputerSys
VALUES(2, 'OptiPlex GX400', 2)
-- Location 3
INSERT INTO #GS_ComputerSys
VALUES(3, 'OptiPlex GX240', 5)
INSERT INTO #GS_ComputerSys
VALUES(3, 'OptiPlex GX260', 100)
INSERT INTO #GS_ComputerSys
VALUES(3, 'Latitude C840', 33)
INSERT INTO #GS_ComputerSys
VALUES(3, 'OptiPlex GX400', 0)
SELECT
Grp,
SSA_Location = CASE dt.Grp WHEN 1 THEN 'GRAND TOTAL' ELSE dt.SSA_Location END,
[OptiPlex GX240],
[OptiPlex GX260],
[Latitude C840],
[OptiPlex GX400],
Total
FROM (SELECT
SSA_Location = s.Location00,
Grp = GROUPING(s.Location00),
[OptiPlex GX240] = SUM(CASE c.Model0
WHEN 'OptiPlex GX240' THEN c.Qty
ELSE 0 END),
[OptiPlex GX260] = SUM(CASE c.Model0
WHEN 'OptiPlex GX260' THEN c.Qty
ELSE 0 END),
[Latitude C840] = SUM(CASE c.Model0
WHEN 'Latitude C840' THEN c.Qty
ELSE 0 END),
[OptiPlex GX400] = SUM(CASE c.Model0
WHEN 'OptiPlex GX400' THEN c.Qty
ELSE 0 END),
[Total] = SUM(c.Qty)
FROM #ssa s
INNER JOIN #GS_ComputerSys c
ON c.ResourceID = s.MachineID
GROUP BY s.Location00
WITH ROLLUP
) dt
ORDER BY Grp, SSA_Location
-
Thanks for all of the input!
The WITH ROLLUP worked perfectly for totaling the columns. Now I need to total the rows. When I use a count(*) it counts all machines at the site, not just the models I'm looking for. So I was trying to do a select within a count statement which I have written below but it doesn't work. Is this syntax even doable?
SELECT DISTINCT
s.Location00 AS 'SSA Location',
SUM(CASE c.Model0
WHEN 'OptiPlex GX240' THEN 1
ELSE 0
END) AS 'OptiPlex GX240',
SUM(CASE c.Model0
WHEN 'OptiPlex GX260' THEN 1
ELSE 0
END) AS 'OptiPlex GX260',
SUM(CASE c.Model0
WHEN 'Latitude C840' THEN 1
ELSE 0
END) AS 'Latitude C840',
SUM(CASE c.Model0
WHEN 'OptiPlex GX400' THEN 1
ELSE 0
END) as 'OptiPlex GX400',
COUNT((Select d.model0 from v_GS_COMPUTER_SYSTEM d where d.model0 = 'OptiPlex GX240' or d.model0 = 'OptiPlex GX400' or d.model0 = 'OptiPlex GX260' or d.model0 = 'Latitude C840')) as 'TOTAL'
FROM v_SSA_INFO s,
v_GS_COMPUTER_SYSTEM c
WHERE c.ResourceID *= s.MachineID
GROUP BY s.Location00
WITH ROlLUP
Thanks! Your help is greatly appreciated!
-
You could put the whole query into another query:
select 'SSA Location', 'OptiPlex GX240', ... , 'OptiPlex GX240'+... as SSATotal
from (
SELECT DISTINCT
s.Location00 AS 'SSA Location',
SUM(CASE c.Model0
WHEN 'OptiPlex GX240' THEN 1
ELSE 0
END) AS 'OptiPlex GX240',
...
)
-
Grasshopper you're concept worked beautifully! I can't believe I didn't think of that sooner - so simple!
Here's what I did to accomplish my goal:
SELECT DISTINCT
s.Location00 AS 'SSA Location',
SUM(CASE c.Model0
WHEN 'OptiPlex GX240' THEN 1
ELSE 0
END) AS 'OptiPlex GX240',
SUM(CASE c.Model0
WHEN 'OptiPlex GX260' THEN 1
ELSE 0
END) AS 'OptiPlex GX260',
SUM(CASE c.Model0
WHEN 'Latitude C840' THEN 1
ELSE 0
END) AS 'Latitude C840',
SUM(CASE c.Model0
WHEN 'OptiPlex GX400' THEN 1
ELSE 0
END) as 'OptiPlex GX400',
COUNT(c.Model0) AS 'TOTAL'
FROM v_SSA_INFO s,
v_GS_COMPUTER_SYSTEM c
WHERE c.ResourceID *= s.MachineID and (c.Model0 = 'OptiPlex GX240' or c.Model0 = 'OptiPlex GX400' or c.Model0 = 'OptiPlex GX260' or c.Model0 = 'Latitude C840')
GROUP BY s.Location00
WITH CUBE
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply