Counting values in same query

1

Hi.
I'm trying to select a total value with count and a value with other criteria when i get some values.
So something like

[code]
select count() as Z
,(select count() from something where X=1 ) as x,
idx
from something
[\code]

So i expect to get something like
idx x z
100 20 10
105 30 22

etc

The problem is that i get that a query returned more than 1 value.
What I will do if i don't get some help is create 2 temp table that will have the idx and values and then merger them
but isn't a simpler way?
Thanks.

2

How about

SELECT 
    idx,
    COUNT(*) AS Z,
    SUM(CASE WHEN X=1 THEN 1 ELSE 0 END) AS X
FROM something 
GROUP BY idx;
1 Like
3

Yes that is it.
Thanks.

4

Hi.
One more thing. Is there a way to put the avegare (%) between the two columns in a third one?

5

Sorry What I mean is if we have X =100 and Y=50 then the return is 50%
If we have X=100 and Y = 70 the return is 70% etc.

6

Here is what I've tried, unfortunately i get a lot of dive by zero errors

cast(
cast(
cast(
    (
     (sum(
    case   when BookingH_strPickupWorkstn is NULL then 0
    when BookingH_strPickupWorkstn  LIKE '%'+'ush'+'%' then 0
     when BookingH_strPickupWorkstn NOT LIKE ''+'K_'+'%' then 1    
      ELSE 0 END)) 
      ) * 100  as decimal(18,2))  /  (sum ( CASE WHEN BookingH_strPickupWorkstn is null then 0 Else 1 END)) as numeric(18,2))  as nvarchar(20)) + '%'
      as POSPrintPercentage
7

OK.
Currently i just put the values into a temp table, update and do a where value > 0