Returning more than one column

[scottyjohn]

Hi all,
I have the following query which returns the values from a view grouped by hour....

SELECT (Left([STARTT],2) & ":00" & "-" & Left([STARTT],2)+1) & ":00" AS [HOUR], Count(SOFTPHONE.BRAND) AS CountOfBRAND1
FROM SOFTPHONE
WHERE (((SOFTPHONE.DIRECTION)="I") AND ((SOFTPHONE.STARTD)="19/03/2006"))
GROUP BY (Left([STARTT],2) & ":00" & "-" & Left([STARTT],2)+1) & ":00";

Which displays.....
HOUR CountOfBRAND1
----------------------------------------
07:00 - 08:00 348
08:00 - 09:00 295

This works fine, but this shows all calls regardless of brand. There are maybe 5 different possibilities for Brand and I would like to return the following table if possible?

HOUR CALLS FOR BRAND1 CALLS FOR BRAND2
----------------------------------------------------
07:00 - 08:00 348 285
08:00 - 09:00 295 321

Can this be done?

John

 

[Remou]

Have you looked at Crosstab queries?

 

[lespaul]

first you'll need to add the Brand to the original query:

SELECT (Left([STARTT],2) & ":00" & "-" & Left([STARTT],2)+1) & ":00" AS [HOUR], SOFTPHONE.BRAND, Count(SOFTPHONE.BRAND) AS CountOfBRAND
FROM SOFTPHONE
WHERE (((SOFTPHONE.DIRECTION)="I") AND ((SOFTPHONE.STARTD)="19/03/2006"))
GROUP BY (Left([STARTT],2) & ":00" & "-" & Left([STARTT],2)+1) & ":00", SOFTPHONE.BRAND
ORDER BY 1, 2;

which will give you:
HOUR Brand CountOfBRAND
----------------------------------------
07:00 - 08:00 Brand1 125
08:00 - 09:00 Brand2 295
07:00 - 08:00 Brand1 216
08:00 - 09:00 Brand2 295

Then take this query and convert it to a cross tab.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design