Click here to Skip to main content
14,971,553 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a table with the following information and I want to count on each allsts
ZONSTS  ALLSTS  DPLSTS  CODPAL              NUMPAL
----------------------------------------------------
Y       20      0       100000000068261815  15408139
Y       20      0       100000000068261822  15408141
Y       20      0       100000000068261839  15408142
Y       20      0       100000000068261846  15408143
Y       20      0       100000000068261853  15408144
Y       20      0       100000000068261860  15408145
Y       20      0       100000000068261877  15408148
Y       9       0       100000000068247963  15408149
Y       9       0       100000000068247956  15408150
Y       9       0       100000000068247949  15408151
Y       9       0       100000000068247932  15408152
Y       9       0       100000000068247925  15408153
Y       9       0       100000000068247918  15408154
Y       20      0       100000000068261884  15408155
Y       9       0       100000000068247901  15408156
Y       9       0       100000000068247895  15408157
Y       9       0       100000000068247888  15408158
Y       20      0       100000000068261891  15408159
Y       9       0       100000000068247871  15408160
Y       9       0       100000000068247864  15408161
Y       9       0       100000000068247857  15408162
Y       9       0       100000000068247833  15408163
Y       20      0       100000000068261907  15408164
Y       9       0       100000000068247819  15408166
Y       9       0       100000000068247796  15408167
Y       9       0       100000000068247772  15408168
Y       20      0       100000000068261914  15408169
Y       9       0       100000000068247789  15408171
Y       20      0       100000000068261921  15408172
Y       20      0       100000000068261938  15408173
Y       20      0       100000000068263154  15408175
Y       20      0       100000000068263161  15408177


What I have tried:

SQL
SELECT 
    ZONSTS,
    dplsts,
    codpal,
    numpal, 
    count(case WHEN  ALLSTS='20' then  'R20' WHEN allsts=''ELSE  end) as RAMPA
FROM
    FGE50CABU3.Gepal 
WHERE
    zonsts='Y' 
AND 
    CODACT='CE1' 
AND 
    ETAPAL='40'
GROUP BY
    zonsts, 
    dplsts,
    codpal,
    numpal
Posted
Updated 24-May-21 22:18pm
v2
Comments
Richard Deeming 24-May-21 7:24am
   
Not at all clear. Your query has a syntax error in the count(...) line, and references columns which don't exist in your source data. And you haven't described what you're actually trying to achieve.

Your GROUP BY will do nothing useful: since numpal is an incrementing number (and probably an IDENTITY column) including it in the GROUP BY list just menas that each group contains a single row.

Have a look here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] and think about exactly what you are trying to aggregate: if what you want is a count of items which have an ALLSTS value of 20 then make it part of the WHERE clause.
If you don't, then I have no idea exactly what you are trying to achieve ...
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
   
Try this:

SQL
SELECT ZONSTS, ALLSTS, ROW_NUMBER() OVER(PARTITION BY ALLSTS ORDER BY NUMPAL) AS RN,
  COUNT(ALLSTS) OVER(PARTITION BY ALLSTS) CNT,
  DPLSTS, CODPAL, NUMPAL
FROM WHatever;


SQL Server 2019 | db<>fiddle[^]

More:
ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs[^]
COUNT (Transact-SQL) - SQL Server | Microsoft Docs[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900