CODEWITHSUNDEEP
sqlvb.netms-access
Isaac Osuna
Isaac Osuna

Reputation: 3

How to sort a query by a row and add multiple columns

I'm making a query to get these results:

Concept  | Variety 1  | Variety 2 | Variety 3
-------- | ---------- | --------- | ---------
Stage 1  |   125.23   |  142.32   |  154.00
State 2  |   100.00   |  100.00   |  100.00

I have 2 DB's to get these results, and this query gives me this result

Query:

SELECT A.Etapa as CEtapa
     , Sum(P.Import) + Sum(P.HExtras) + Sum(P.Importunidades) as Total 
FROM Percepciones as P
   , [\SYS21\Camel\COSTOS.MDB].Lotes as L
   , [\SYS21\Camel\COSTOS.MDB].Actividades as A 
WHERE P.Apl2__ =  L.Numero 
AND P.Apl3__ = A.Numero
GROUP BY A.Etapa
ORDER BY A.Etapa

Result:

Concept  | Variety 1  
-------- | ---------- 
Stage 1  |   125.23   
State 2  |   100.00

The problem is that I have N amount of varieties and I need the query to do that, I'm using .NET to show these results. So I thought, "Why not just create a loop to add every variety." But, the problem that I'm having is that they're not getting calculated by stage, this is my test query:

SELECT A.Etapa as CEtapa
    ,(SELECT Total 
      FROM (SELECT Sum(P.Import) + Sum(P.HExtras) + Sum(P.Importunidades) as Total 
            FROM Percepciones as P
               , [\SYS21\Camel\COSTOS.MDB].Lotes as L
               , [\SYS21\Camel\COSTOS.MDB].Actividades as A 
            WHERE L.Numero = P.Apl2__
            AND Variedad = L.Variedad
            AND P.Apl3__ = A.Numero
        )
      ) as Variedad1 
FROM Percepciones as P
   , [\SYS21\Camel\COSTOS.MDB].Lotes as L
   , [\SYS21\Camel\COSTOS.MDB].Actividades as A 
WHERE P.Apl2__ =  L.Numero
AND P.Apl3__ = A.Numero 
GROUP BY A.Etapa

What I would loop on .Net to concatenate would be:

SELECT Total
FROM (SELECT Sum(P.Import) + Sum(P.HExtras) + Sum(P.Importunidades) as Total
      FROM Percepciones as P
         , [\SYS21\Camel\COSTOS.MDB].Lotes as L
         , [\SYS21\Camel\COSTOS.MDB].Actividades as A
      WHERE L.Numero = P.Apl2__ 
      AND Variedad = L.Variedad
      AND P.Apl3__ = A.Numero
     )
 ) as Variedad1

Sadly this gives me the same result on every stage because I'm not comparing the stage on the WHERE clause, because I don't know how to use the stage from the main query on the subquery.

Any idea would be greatly appreciated.

Upvotes: 0

Views: 101

Answers (1)

Parfait
Parfait

Reputation: 107737

Consider MS Access SQL's unique crosstab query which wraps a TRANSFORM and PIVOT clause around an aggregate GROUP BY query to dynamically pivot aggregations across groupings.

Below groups by Etapa and pivots Variedad aggregating your calculated Total field. Do note the Access limit of N=255 columns in any query resultset. Also, below uses the explicit join with INNER JOIN (ANSI '92 standard of 25 years) as opposed to your use of the implicit join with comma-separated tables joined by WHERE clause. For multiple joins, Access SQL does require parentheses pairings.

TRANSFORM Sum(P.Import) + Sum(P.HExtras) + Sum(P.Importunidades) as Total
SELECT A.Etapa as CEtapa 
FROM (Percepciones as P
INNER JOIN [\SYS21\Camel\COSTOS.MDB].Lotes as L
   ON P.[Apl2__] =  L.[Numero]) 
INNER JOIN [\SYS21\Camel\COSTOS.MDB].Actividades as A 
   ON P.[Apl2__] =  A.[Numero]
GROUP BY A.Etapa
PIVOT L.Variedad

And should you want to limit the columns add IN to PIVOT:

PIVOT L.Variedad IN ('Variety 1', 'Variety 3', 'Variety 5')

Upvotes: 1

Related Questions