What Should be the Query for this

ramk_1978 · December 16th, 2004, 12:38 PM

Hi,

This is Ramkumar. I was given a question which was as follows and i am need in help to solve this puzzle.

Given Table: SalesMan Table

SalesManId Period TotalSales
S1 H1 10000
S1 H2 5000
S2 H1 3000
S2 H2 9000

I want to write a query to get the following output:

SalesManId H1 H2 TotalSales
S1 10000 5000 15000
S2 3000 9000 12000

A.D.Ramkumar

Jonax · December 16th, 2004, 02:05 PM

This could probably be done more elegant, but here's a shot at it...

Code:

SELECT 
T1.SalesManId, 
(
    SELECT 
    MAX(TotalSales) 
    FROM tbTest T2 
    WHERE Period = 'H1' AND T1.SalesManId = T2.SalesManId
) AS 'H1',
(
    SELECT 
    MAX(TotalSales) 
    FROM tbTest T3 
    WHERE Period = 'H2' AND T1.SalesManId = T3.SalesManId
) AS 'H2',
(
    SELECT 
    MAX(TotalSales) 
    FROM tbTest T2 
    WHERE Period = 'H1' AND T1.SalesManId = T2.SalesManId
) +
(
    SELECT 
    MAX(TotalSales) 
    FROM tbTest T3 
    WHERE Period = 'H2' AND T1.SalesManId = T3.SalesManId
) AS 'TotalSales'
FROM tbTest T1
GROUP BY SalesManId

edcaru · December 18th, 2004, 09:45 AM

in ms access you can use a crosstab query

TRANSFORM Max(Data.Period) AS MaxOfPeriod
SELECT Data.Sales, Sum(Data.Period) AS [Total Of Period]
FROM Data
GROUP BY Data.Sales
PIVOT Data.Manid