SQL Query:

Does anyone know how to write query to get totals of each group and their percentage. Below is the example. Ct is count, Per is percentage, Pri is priority level Thanks a lot!

Region      Ct_Tkts       Per_Region   ct_prior_3          ct_prio_2      Per_Prio_3                Percentage_Priority_2      

N.Amer      1000          20%                 500                       500                       10%                                          10%
S.Amer      1000 20%      1000      300 20%      5%      
Asia      500      10%      
Europe      5000 50%

This is what i tried, i keep getting errors!!

select REGION,COUNT(*) as TotalCount,
(COUNT(*) * 1.0) / SUM(COUNT(*)) over() as Percent
FROM [Xyz] h
JOIN
[ABC] p
ON p.PARENT_TICKET_ID = h.CASE_ID
Tulip_23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Oracle or SQL Server?

In Oracle I would use an inline view to get the counts.  Then you can do whatever you want with them in the outside query.

Something like this:
select total_count, region_count/total_count
from
(
   select region, count(*), count(*) over() total_count from sometable
)

Open in new window

Not sure if SQL Server allows inline views or not.
Tulip_23Author Commented:
Hi, thanks for response. I am using BigQuery and tried below

select p.service_region,total_count, count(*)/total_count
from
(
   select p.service_region, count(*), count(*) over() total_count from [XYZ] p
)
group by p.service_region , total_count

I get the error below
Error: SELECT clause has mix of aggregations 'f0_' and fields 'p.service_region' without GROUP BY clause
slightwv (䄆 Netminder) Commented:
Never heard of BigQuery.  Can only comment on Oracle syntax but what you posted has issues that I would expect across and SQL platform.

I missed the group by.  It should be on the inner query.

select total_count, region_count/total_count
from
(
   select region, count(*), count(*) over() total_count from sometable group by region
)

Also, if you alias the table on the inner query, you cannot reference it in the outer query.
Mike EghtebasDatabase and Application DeveloperCommented:
For CTE solution, try
;With CTE_1
As
(
Select Region
        ,  Sum(Ct_Tkts) As Region_Ct_Tkts
From #t
WHERE Region= 'N.Amer'
Group By region),    -- if Region is unque, delete Group By
CTE_2
AS
(
Select   Sum(Ct_Tkts) As total_Ct_Tkts
From #t
)
Select region
         , Region_Ct_Tkts As Denominator
        , total_Ct_Tkts As Numerator
         , cast((Select Region_Ct_Tkts From CTE_1) * 100.0 / total_Ct_Tkts as decimal(10,2))  AS Percentage
From CTE_1, CTE_2

Open in new window


returns:
region    Region_Ct_Tkts     total_Ct_Tkts          Percent
N.Amer      1000                       7500                       13.33

With this test data:
create table #t(Region varchar(15)
              , Ct_Tkts int
			  , Per_Region decimal(4,2)
			  , ct_prior_3 int
			  , ct_prio_2 int
			  , Per_Prio_3 decimal(4,2)
			  , Percentage_Priority_2 decimal(4,2))      
Insert #t(Region, Ct_Tkts, Per_Region, ct_prior_3, ct_prio_2, Per_Prio_3, Percentage_Priority_2) values 
('N.Amer', 1000, 0.2, 500, 500, 0.1, 0.1)
, ('S.Amer', 1000, 0.2, 1000, 300, 0.2, 0.05)
, ('Asia', 500, 0.1, null, null, null, null)
, ('Europe', 5000, 0.5, null, null, null, null)

select * From #t;

Open in new window


In the above code only Region   and Ct_Tkts are used.
Region   Ct_Tkts   Per_Region    ct_prior_3    ct_prio_2    Per_Prio_3    Percentage_Priority_2
N.Amer	 1000	   0.20 	 500	        500	        0.10	       0.10
S.Amer	 1000	   0.20	         1000	        300	        0.20	       0.05
Asia	 500	   0.10	         NULL	       NULL	         NULL	       NULL
Europe	 5000	   0.50	         NULL          NULL 	       NULL	   NULL

Open in new window


Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.