Could someone help me how to group? I have this query, what I need is to group the amount by "condition" and by "group_program" that is
I have this query:
SELECT at_ce.condicion, gpr.grupo_programa FROM at_cons_externa at_ce
INNER JOIN gen_programa gpr ON at_ce.id_programa = gpr.id_programa
I can group with this line:
select condicion, count(*) as cantidad from at_cons_externa group by condicion
But I would also like to group "group_program", in the same sentence and with the amount that belongs to each one, I hope you can help me, Thanks...
To be able to use the aggregation functions COUNT, SUM, etc. you must group all the other columns that are not counted to count, add, etc.
Example:
You can introduce the two select in the from:
The accepted answer contains what you need. Just to pay for the comment I made regarding the "pivot table", I put here an option to generate it.
As an example, the "row headers" will be the values of
condicion
and the column headers will be the values ofgrupo_programa
(I generated 100 random values to test this working fine).First, let's put the values of interest into a temporary table, to save us from writing the same query over and over again
For this solution to work, it is necessary to ensure that the function
GROUP_CONCAT()
can return long strings. Generally, the value that I propose here is more than enough:Now comes the cool part: We are going to generate a list of expressions that add up to 1 for each column value
grupo_programa
present in the table, and zero otherwise. These expressions will be stored in a variable (@sql
):Let's now complete the query that we are generating:
If you want to see how your complete query turned out, you can check it like this:
Now yes, let's get the values: Prepare and execute the query we just created:
The result:
That last row (with the value
NULL
in the columncondicion
) contains the total for each column (this value is generated when you useGROUP BY ... WITH ROLLUP
; if you don't need it, just removeWITH ROLLUP
).When you're done, don't forget to "clean" the prepared statement:
I hope this solution is useful to you.
Cheers