Running two queries to get two columns

[scottyjohn]

Hi all,
I have 3 tables, A holds all the transaction details including empid. Tables B and C hold the EMPID lists for two seperate sites we have. I can run a query in access which shows me a count of certain call types per site based on the empid tables B or C, but only as seperate queries. Is there any way that I can have the calltype down the first column, then calls from that calltype for site A then site B so three columns in total. Also need to show a zero or null if there have been no calls of that type for that site.
Any help would be greatly appreciated!

John
ski_69@hotmail.com

 

[r937]

try this --

select empid
     , sum(Bcount) as Btotal
     , sum(Ccount) as Ctotal
  from (     
       select A.empid
            , count(*) as Bcount
            , 0        as Ccount
         from A
       inner
         join B
           on A.empid = B.empid  
       group
           by A.empid
       union all
       select A.empid
            , 0        as Bcount
            , count(*) as Ccount
         from A   
       inner
         join C
           on A.empid = C.empid      
       group
           by A.empid
       ) as dt
group
    by empid

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[scottyjohn]

Thanks Rudy,
To give you further info, A typical record in my table A (called SOFTPHONE_V_CALLTYPE_CDR) would have the EMPID (employee ID) of the agent who took that call, the brand of the call as field BRAND. The other 2 tables are to allow me to narrow the query from the SOFTPHONE_V_CALLTYPE_CDR table to be specific to a site, i.e., agents at Glasgow are listed in the table GLASGOWAGENTS, and agents at Swansea are listed in SWANSEAAGENTS. What I need is a column for each site which gives the total calls for each BRAND, so PRODUCT named TH_MAIN would have entries next to it showing the total calls at each site. Thanks for your patience

John
ski_69@hotmail.com

 

[r937]

you're welcome

glad you liked it



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[PHV]

Something like this ?
SELECT A.EMPID, A.BRAND, Sum(IIf(IsNull(B.EMPID),0,1) As Glasgow, Sum(IIf(IsNull(C.EMPID),0,1) As Swansea
FROM (SOFTPHONE_V_CALLTYPE_CDR A
LEFT JOIN GLASGOWAGENTS B ON A.EMPID=B.EMPID)
LEFT JOIN SWANSEAAGENTS C ON A.EMPID=C.EMPID
GROUP BY A.EMPID, A.BRAND

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scottyjohn]

Hi there,
Am trying the following query but get a syntax error message

SELECT
SOFTPHONE_V_CALLTYPE_CDR.EMPID,
SOFTPHONE_V_CALLTYPE_CDR.BRAND,
Sum(IIf(IsNull(GLASGOWAGENTS.EMPID),0,1) As Glasgow,
Sum(IIf(IsNull(SWANSEAAGENTS.EMPID),0,1) As Swansea
FROM (SOFTPHONE_V_CALLTYPE_CDR A LEFT JOIN GLASGOWAGENTS B ON SOFTPHONE_V_CALLTYPE_CDR.EMPID=GLASGOWAGENTS.EMPID)
LEFT JOIN SWANSEAAGENTS C ON SOFTPHONE_V_CALLTYPE_CDR.EMPID=SWANSEAAGENTS.EMPID
GROUP BY SOFTPHONE_V_CALLTYPE_CDR.EMPID, SOFTPHONE_V_CALLTYPE_CDR.BRAND

Any idea where I am going wrong?

John
ski_69@hotmail.com

 

[PHV]

Oops, sorry for the typo:
SELECT A.EMPID, A.BRAND, Sum(IIf(IsNull(B.EMPID),0,1)[highlight])[/highlight] As Glasgow, Sum(IIf(IsNull(C.EMPID),0,1)[highlight])[/highlight] As Swansea
FROM (SOFTPHONE_V_CALLTYPE_CDR A
LEFT JOIN GLASGOWAGENTS B ON A.EMPID=B.EMPID)
LEFT JOIN SWANSEAAGENTS C ON A.EMPID=C.EMPID
GROUP BY A.EMPID, A.BRAND

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244