How to use a single statement ?

Question

Post reply

How to use a single statement ?

Pedro Reis-267786

Mr or Mrs. 500

Points: 590
More actions
September 6, 2006 at 8:54 am

#103229

Hi,

I have 2 SQL statements :

Select count(A) as total       from TABLE
Select count(A) as sla_ok   from TABLE where A=’1’       (A is a Boolean)

The result is like
   total
1 6958

   sla_ok
1 6764

I want to a result like   :

Total             sla_ok             % (sla_ok / total)
6958              6764                0,97

How can I achieve that , using a single statement ?

Thanks in advance …

Viewing 15 posts - 1 through 15 (of 30 total)

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

David.Poole SSC Guru Points: 76035 More actions · Answer 1

SELECT COUNT(A) AS Total , SUM(CAST (A AS INT)) AS SLA_OK

FROM TABLE

LinkedIn Profile

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 2

Hummm,

I’m sorry I didn’t understand the cast part. Why convert the type ?

I just want to select the total set from a table , and a part of the set onde the same row….

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You can't sum on a bit datatype as far as I remember!!

Anyways isn't this what you needed?

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 4

No, I dont want to sum want to COUNT, the type is char ‘0’ ‘1’ ‘A’…

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

So you have a boolean char .

SELECT COUNT(A) AS Total , SUM(CASE WHEN 'A' = '1' THEN 1 ELSE 0 END) AS SLA_OK FROM TABLE

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 6

Hi,

Ok, assume it´s not a boolean ...

i just wanto to count one set if A='X' and the total set

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Did you try my query??? It's what it's supposed to do!

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 8

ok i made on adapation and it worked fine.

Tkanks a lot!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Oops, ya works better like that

SUM(CASE WHEN A = '1'

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 10

One thing more- I want to create a third column with the result of the expression cdu_SLA_OK/Total

SELECT COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)

AS SLA_OK, result = SLA_OK/Total

But i get the error :

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name 'SLA_OK'.

Server: Msg 207, Level 16, State 1, Line 1

Invalid column name 'Total'.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

You can't do that on sql server like Acces let you do. You must reuse the count and sum operations for the total >>

SELECT COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)

AS SLA_OK, SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END) / COUNT(cdu_slaok)*1.0 AS Total FROM...

the 1.0 is to cast to a decimal so that you don't always get 0 for the total column

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 12

Pedro Reis-267786

Mr or Mrs. 500

Points: 590

September 6, 2006 at 10:45 am

#658805

i get .0

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Doh, operations priority... the cast is made after the division; easy fix :

SELECT 1.0 * COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)

AS SLA_OK, SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END) / COUNT(cdu_slaok) AS Total FROM...

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 14

Pedro Reis-267786

Mr or Mrs. 500

Points: 590

September 6, 2006 at 11:17 am

#658815

same problem