Good afternoon.

The the query --- whose code you find at the end of this message
--- yields a result such as

tasks group days
17 CD 1
5 CX 1
20 RA 1
37 TX 1
21 CD 2
14 CX 2
7 RA 2
....

Read this result as:

The group CD has 17 tasks sitting for 1 day
The group CX has 5 tasks sitting for 1 day.
...

There are a few more groups, however. There's IE, ST and others.
These don't appear because, above, the answer would be 0 for
them.

Is it possible to get a row with 0 instead of a no record? How
would you handle this problem? Feel free to show me a different
approach.

Here's the result I want:

tasks group days
17 CD 1
5 CX 1
20 RA 1
37 TX 1
0 ST 1
0 IE 1
0 RD 1

If I could get that one query that queries just for days = 1, I'd
be okay with issuing various queries to get all the days I want.
That is, I'd query for days = 1, then again for days = 2 and so
on up to, say, days = 8.

This would make my life much easier when it comes to displaying
that to the user --- because I want to display the zeros in a
table where each group appear on a line of the table, the days
appear as column headers and the total of tasks appear as
elements of the table-matrix.

Here's the query I wrote that produce the result above.

SELECT
COUNT(tram_id_local) AS N_PER_AREA,
tram_cod_area_viabilidade,
TEMPO_NO_POSTO
FROM

(SELECT
tramitacoes.tram_id_local
,tramitacoes.tram_cod_area_viabilidade
,CASE TIMESTAMPDIFF(DAY, tramitacoes.tram_data_hora_chegada,now())
WHEN 0 THEN '1'
WHEN 1 THEN '1'
WHEN 2 THEN '2'
WHEN 3 THEN '3'
WHEN 4 THEN '4'
WHEN 5 THEN '5'
WHEN 6 THEN '6'
WHEN 7 THEN '7'
ELSE '8+'
END AS TEMPO_NO_POSTO

FROM tramitacoes
INNER JOIN pedidos ON pedidos.ped_id_local = tramitacoes.ped_id_local
LEFT JOIN projetos ON projetos.tram_id_local = tramitacoes.tram_id_local

WHERE 1=1
AND isNULL(projetos.usu_id)
AND isNULL(tramitacoes.tram_data_hora_resposta_gpc)
AND tramitacoes.tram_cod_area_viabilidade IN
('CD', 'CX', 'IE', 'RA', 'RD', 'ST', 'TX')
AND tramitacoes.tram_cod_posto = 'PRB') AS N_PER_AREA_SEM_DONO

GROUP BY
TEMPO_NO_POSTO,
tram_cod_area_viabilidade

On Jan 4, 12:15*pm, Daniel Bastos <dbas... (AT) toledo (DOT) com> wrote:

why are you using a sub-query?

SELECT
* * *count(tramitacoes.tram_id_local)
* * ,tramitacoes.tram_cod_area_viabilidade
* * ,CASE TIMESTAMPDIFF(DAY,
tramitacoes.tram_data_hora_chegada,now())
* * * *WHEN 0 THEN '1'
* * * *WHEN 1 THEN '1'
* * * *WHEN 2 THEN '2'
* * * *WHEN 3 THEN '3'
* * * *WHEN 4 THEN '4'
* * * *WHEN 5 THEN '5'
* * * *WHEN 6 THEN '6'
* * * *WHEN 7 THEN '7'
* * * *ELSE '8+'
* * *END AS TEMPO_NO_POSTO
* * *FROM tramitacoes
* * * *INNER JOIN pedidos ON pedidos.ped_id_local =
tramitacoes.ped_id_local
* * * *LEFT JOIN projetos ON projetos.tram_id_local =
tramitacoes.tram_id_local
* * *WHERE 1=1
* * * *AND isNULL(projetos.usu_id)
* * * *AND isNULL(tramitacoes.tram_data_hora_resposta_gpc)
* * * *AND tramitacoes.tram_cod_area_viabilidade IN
* * * * * * ('CD', 'CX', 'IE', 'RA', 'RD', 'ST', 'TX')
* * * *AND tramitacoes.tram_cod_posto = 'PRB')
GROUP BY tramitacoes.tram_cod_area_viabilidade, TEMPO_NO_POSTO

On Thursday, January 5, 2012 12:42:18 AM UTC-2, onedbguru wrote:

I had not noticed I could avoid it.

On Thu, 5 Jan 2012 03:04:35 -0800 (PST), Daniel Bastos wrote:
When you're just collecting the counts, especially if that's all you're
really interested in is various counts, then you can just include the
counts derivation in the underpinning query and get the right answer.
For example, you have a table that records date and duration of phone
calls from many phone lines, and you wish to know the number of
calls made each month. That's only a single layer of summation, and SQL
supports that very nicely.

If you need to do some other aggregating function on the counts to get
the results you want, like averaging the number number of phone calls
per month by per year, then you need a single subquery to get that.
Counts come from the underpinning subquery, then the averaging happens
on the groups in outer query.

--
Lisa: There's going to be sex, drugs, rock'n'roll. Chips, dips,
chains, whips.
-- "Weird Science"