Combine two queries

13 May 2005 3:30 PM

scott

Hello, i am an SMS guy trying to write some SQL queries and having little
luck. I have two queries. The first returns the dept code which in my case
is the first 2 letters a computer and a count of computers with that dept
code(see below).
Total counts by dept code
SELECT     LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
FROM         v_R_System
GROUP BY LEFT(Name0, 2)
Second is a query that returns the dept code and a count of machines that
have the client installed.
Totals counts installed by dept code
SELECT     LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
FROM         v_R_System where client0=1
GROUP BY LEFT(Name0, 2)

My question is this... How the heck can I combine the two into one query so
I return Dept code, total machines, and total machines with client?

Thanks for helping out this rookie...
scott

13 May 2005 3:35 PM

Anith Sen

You can use a CASE like:

SELECT LEFT( Name0, 2 ) AS "dept_code",
COUNT( * ) AS "total_machines",
SUM( CASE WHEN client0 = 1 THEN 1 ELSE 0 END ) AS "client0_count"
FROM v_R_System
GROUP BY LEFT( Name0, 2 ) ;

--
Anith

13 May 2005 3:44 PM

Alejandro Mesa

Try,

SELECT
    LEFT(Name0, 2) AS [dept Code],
    client0,
    COUNT(*) AS [Total Machines],
    (select count(*) from v_R_System as t1 where LEFT(t1.Name0, 2) =
LEFT(v_R_System.Name0, 2)) as total_dept_comp
FROM
    v_R_System
GROUP BY
    LEFT(Name0, 2),
    client0
order by
    LEFT(Name0, 2),
    client0

AMB

Show quote

"scott" wrote:

> Hello, i am an SMS guy trying to write some SQL queries and having little
> luck. I have two queries. The first returns the dept code which in my case
> is the first 2 letters a computer and a count of computers with that dept
> code(see below).
> Total counts by dept code
> SELECT     LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
> FROM         v_R_System
> GROUP BY LEFT(Name0, 2)
> Second is a query that returns the dept code and a count of machines that
> have the client installed.
> Totals counts installed by dept code
> SELECT     LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
> FROM         v_R_System where client0=1
> GROUP BY LEFT(Name0, 2)
>
> My question is this... How the heck can I combine the two into one query so
> I return Dept code, total machines, and total machines with client?
>
> Thanks for helping out this rookie...
> scott
>