query resumen

Hol experts, I have a table inthe database with 3 columns (observanciaRegistro, manejoGramatical, utilizacionFormalismos).
Each of these columns can have the values 1-5.
I need to do a query to tell me the number of times under the number 1, number 2, etc
I attached two files data and query
number.JPG
reporte-deseado.JPG
enrique_aeoAsked:
Who is Participating?
 
JoeNuvoConnect With a Mentor Commented:
Just in case nobody else provide better solution.
Here is the simple fixed to replace NULL with zero

SELECT
[Title],
COALESCE([5],0) as [5],
COALESCE([4],0) as [4],
COALESCE([3],0) as [3],
COALESCE([2],0) as [2],
COALESCE([1],0) as [1]
FROM
(
SELECT * FROM
(
SELECT 'observanciaRegistro' as [Title], observanciaRegistro as [Score], Count(*) as CNT FROM [tablename] GROUP BY observanciaRegistro
UNION ALL
SELECT 'manejoGramatical' , manejoGramatical, Count(*) FROM [tablename] GROUP BY manejoGramatical
UNION ALL
SELECT 'utilizacionFormalismos', utilizacionFormalismos, Count(*) FROM [tablename] GROUP BY utilizacionFormalismos
) As SubQuery
PIVOT (SUM(CNT) FOR [Score] IN ([1],[2],[3],[4],[5])) As SubPivot
) As Result
0
 
JoeNuvoCommented:
Replace [tablename] with name of your table.

SELECT * FROM
(
SELECT 'observanciaRegistro' as [Title], observanciaRegistro as [Score], Count(*) as CNT FROM [tablename] GROUP BY observanciaRegistro
UNION ALL
SELECT 'manejoGramatical' , manejoGramatical, Count(*) FROM [tablename] GROUP BY manejoGramatical
UNION ALL
SELECT 'utilizacionFormalismos', utilizacionFormalismos, Count(*) FROM [tablename] GROUP BY utilizacionFormalismos
) As SubQuery
PIVOT (SUM(CNT) FOR [Score] IN ([1],[2],[3],[4],[5])) P
0
 
JoeNuvoCommented:
p.s. in order to get result same as your image, you may need to change

PIVOT (SUM(CNT) FOR [Score] IN ([1],[2],[3],[4],[5])) P

to be

PIVOT (SUM(CNT) FOR [Score] IN ([5],[4],[3],[2],[1])) P
0
 
enrique_aeoAuthor Commented:
it is working, But, instead of null 0 I want it...
null.JPG
0
 
lance86Commented:
an NVL() or DECODE() might do the trick.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.