CODEWITHSUNDEEP
sqlt-sql
wootscootinboogie
wootscootinboogie

Reputation: 8705

Add a local variable to every row of a result set

I've been playing around with a large database on which I'm done many summary statistics. I can't help but think there's got to be a quicker way than what I'm doing.

Take for instance the following query:

select count(*), thing1, thing2, thing3 from myTable
group by thing1, thing2, thing3

enter image description here

The 1=true and 0=false. If I wanted to add a a local variable from the dataset (say select @total=count(*) from peopleTable) and divided each row in counts by @total, is there a typical SQL solution for this? I know how to do this on a per-row basis, but not all at once like this.

Upvotes: 0

Views: 1566

Answers (1)

YvesR
YvesR

Reputation: 6232

EDITED:

Now I think you want this:

DECLARE @total int;
SET @total = (SELECT COUNT(*) FROM peopleTable)
SELECT COUNT(*) AS count_column
     , CAST(COUNT(*) AS float) / @total AS percent_total
     , @total AS total_ppl
     , thing1, thing2, thing3 
FROM myTable
GROUP BY thing1, thing2, thing3

This query give you COUNT of your grouped records, percentage count regarding to total ppl and total ppl.

Upvotes: 2

Related Questions