1

I need to do a COUNT for each month in a year so I do this:

SELECT 
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '01' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS jan,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '02' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS fev,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '03' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS mar,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '04' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS abr,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '05' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS mai,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '06' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS jun,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '07' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS jul,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '08' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS ago,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '09' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS stb,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '10' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS out,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '11' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS nov,
(
    SELECT  COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
    to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '12' AND
    {PRODUTO}.[ProdutoTipoId] = @TipoProduto
) AS dez

FROM dual

Is there a way to simplify this? Cheers.

Improve this question
5
  • 2
    This isn't native Oracle SQL. Is it run through a sort of preprocessor before executing it? Commented Dec 6, 2011 at 11:48
  • It's definitely not a valid SQL statement at all. The use of the non-standard [] "quotes" seem to indicate that this is actually a SQL Server statement. But the usage of the {} is not possible in any DBMS I know Commented Dec 6, 2011 at 11:58
  • Yes..this isn't standard SQL... Between {} we have table name, between [] attributes and @xxx its variables. Before querying the BD the statement is pre-processed to actually use real names. Commented Dec 6, 2011 at 12:01
  • 1
    Interestingly, nobody has proposed a PIVOT query. This would be a prime application for it. Commented Dec 6, 2011 at 12:16
  • 1
    @Codo: Oracle (at least up to 10g) doesn't include a PIVOT clause. Commented Dec 6, 2011 at 12:20

3 Answers 3

Reset to default
2

Try:

SELECT COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '01' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS jan,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '02' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS fev,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '03' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS mar,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '04' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS abr,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '05' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS mai,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '06' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS jun,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '07' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS jul,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '08' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS ago,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '09' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS stb,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '10' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS out,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '11' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS nov,
       COUNT(CASE WHEN to_char({ENCOMENDA}.[DataSubmissao], 'MM') = '12' 
                  THEN {ENCOMENDA_LINHA}.[Quantidade]) END) AS dez
FROM (( {ENCOMENDA}
INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
{PRODUTO}.[ProdutoTipoId] = @TipoProduto

Includes a fairly generic method of pivoting the results. Note that since there is no restriction on year, data from multiple years could be appearing in each month's results - this would also be true of the original query.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Actually I will need to restrict the year... I just have to add it in the WHERE clause right?
@JoaoHeleno: Yes, as far as I can tell.
2

This is a pivot operation. In 11G:

sample:

SELECT *
  FROM (SELECT job, deptno, sum(sal) sal FROM emp GROUP BY job, deptno)
         PIVOT ( sum(sal) FOR deptno IN (10, 20, 30, 40) );

JOB               10         20         30         40
--------- ---------- ---------- ---------- ----------
CLERK           1300       1900        950
SALESMAN                              5600
PRESIDENT       5000
MANAGER         2450       2975       2850
ANALYST

with your code:

SELECT  * 
  FROM 
    ( SELECT 
      COUNT( {ENCOMENDA_LINHA}.[Quantidade])
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} 
       ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} 
       on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE 
      {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
      {PRODUTO}.[ProdutoTipoId] = @TipoProduto
    PIVOT ( COUNT( {ENCOMENDA_LINHA}.[Quantidade]) 
            FOR to_char({ENCOMENDA}.[DataSubmissao], 'MON') IN
              ('JAN','FEB','MAR', ..., 'DEC')
    )

For previous version, sample:

SELECT  *
  FROM  (SELECT job,
                sum(decode(deptno,10,sal)) DEPT10,
                sum(decode(deptno,20,sal)) DEPT20,
                sum(decode(deptno,30,sal)) DEPT30,
                sum(decode(deptno,40,sal)) DEPT40
           FROM scott.emp
       GROUP BY job)
ORDER BY 1;

for your code:

SELECT  * 
  FROM 
    ( SELECT 
        COUNT( decode( to_char({ENCOMENDA}.[DataSubmissao], 'MON'), 
                       'JAN',
                       {ENCOMENDA_LINHA}.[Quantidade]
                      )
              ) JAN,
        COUNT( decode( to_char({ENCOMENDA}.[DataSubmissao], 'MON'), 
                       'FEB',
                       {ENCOMENDA_LINHA}.[Quantidade]
                      )
              ) FEB,
        ...
        COUNT( decode( to_char({ENCOMENDA}.[DataSubmissao], 'MON'), 
                       'DEC',
                       {ENCOMENDA_LINHA}.[Quantidade]
                      )
              ) DEC,
    FROM (( {ENCOMENDA}
    INNER JOIN {ENCOMENDA_LINHA} 
       ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
    INNER JOIN {PRODUTO} 
       on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
    WHERE 
      {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
      {PRODUTO}.[ProdutoTipoId] = @TipoProduto
    )

disclaimer: not tested. Check for month short names in your language.

Improve this answer

Comments

1

Create a temporary table with the needed values (month and quantity).

SELECT to_char({ENCOMENDA}.[DataSubmissao], 'MM'), COUNT( {ENCOMENDA_LINHA}.[Quantidade])
FROM (( {ENCOMENDA}
INNER JOIN {ENCOMENDA_LINHA} ON ( {ENCOMENDA}.[Id] = {ENCOMENDA_LINHA}.[EncomendaId] ))
INNER JOIN {PRODUTO} on ( {ENCOMENDA_LINHA}.[ProdutoId] = {PRODUTO}.[Id] ))
WHERE {ENCOMENDA}.[EstadoId] = @EstadoEncomenda AND
{PRODUTO}.[ProdutoTipoId] = @TipoProduto

And afterwords select what you need from it.

Improve this answer

2 Comments

You'll need a GROUP BY for this approach.
Could I use dual for this effect? Could I insert values 1 to 12 into dual? How would the query be using this approach?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.