Summing in a table

Question

Post reply

Summing in a table

Sachin 80451

SSCommitted

Points: 1642
More actions
May 17, 2012 at 9:24 am

#147227

I need to work out the percentage of the values from sales. I have split my data into the necessary group like:
status Total ALLTOTAL
1. On Hold 29?
2. Attempting to Contact3
3. In dialogue with customer1
4. 1st Appointment made1
9. Quoted 15
Im trying to get the all total. I have basically got the total by counting status. But want to get the ALLTOTAL column to be 49 all the way down. Is there a way of doing this?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Reading the following should help:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

Sachin 80451 (5/17/2012)
I need to work out the percentage of the values from sales. I have split my data into the necessary group like:
status Total ALLTOTAL
1. On Hold 29?
2. Attempting to Contact3
3. In dialogue with customer1
4. 1st Appointment made1
9. Quoted 15
Im trying to get the all total. I have basically got the total by counting status. But want to get the ALLTOTAL column to be 49 all the way down. Is there a way of doing this?

SUM() OVER (ORDER BY (SELECT NULL))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 3

This would work as well 🙂

SUM([column]) OVER () AS AllTotal

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

DBA_Dom SSCrazy Points: 2902 More actions · Answer 4

One other way that it could be done as well is

select status, count(status) as total, (select count(*) from table) as alltotal

from table

group by status

Dwain Camps SSC Guru Points: 86978 More actions · Answer 5

Here's yet another way:

DECLARE @t TABLE (status VARCHAR(30))

INSERT INTO @t

SELECT 'On Hold' UNION ALL SELECT 'On Hold' UNION ALL SELECT 'On Hold'

UNION ALL SELECT 'On Hold' UNION ALL SELECT 'On Hold' UNION ALL SELECT 'On Hold'

UNION ALL SELECT 'Attempting to Contact'

UNION ALL SELECT 'In dialogue with customer'

UNION ALL SELECT '1st Appointment made'

UNION ALL SELECT 'Quoted'

;WITH CWR AS (

SELECT status, COUNT(status) AS Count

FROM @t

GROUP BY status WITH ROLLUP)

SELECT CASE WHEN status IS NULL THEN 'All Total' ELSE status END as Status, count

FROM CWR

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 6

dwain.c (5/20/2012)
Here's yet another way:
DECLARE @t TABLE (status VARCHAR(30))
INSERT INTO @t
SELECT 'On Hold' UNION ALL SELECT 'On Hold' UNION ALL SELECT 'On Hold'
UNION ALL SELECT 'On Hold' UNION ALL SELECT 'On Hold' UNION ALL SELECT 'On Hold'
UNION ALL SELECT 'On Hold' UNION ALL SELECT 'On Hold' UNION ALL SELECT 'On Hold'
UNION ALL SELECT 'Attempting to Contact'
UNION ALL SELECT 'Attempting to Contact'
UNION ALL SELECT 'Attempting to Contact'
UNION ALL SELECT 'In dialogue with customer'
UNION ALL SELECT '1st Appointment made'
UNION ALL SELECT 'Quoted'
;WITH CWR AS (
SELECT status, COUNT(status) AS Count
FROM @t
GROUP BY status WITH ROLLUP)
SELECT CASE WHEN status IS NULL THEN 'All Total' ELSE status END as Status, count
FROM CWR

Nice alternative using WITH ROLLUP. Thank you.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA