Group By Count Where Condition

[123]

Thank you for your help and answer

I have this query:

Company Name Department City

IBM Software Paris

IBM Software Paris

Microsoft Software London

Microsoft Software London

HP Hardware Madrid

HP Hardware Geneva

Oracle Software London

(Where city = Paris or London)

=======================================

I want this Query Convert to

Company Name Department City

IBM Software 2

Microsoft Software 2

HP Hardware 0

Oracle Software 1





My Notes: I want using Group By and Count using Condition (Paris and
London) if the condition not in some company Group the company and display 0

*** If you could the reuslut should be like the above table

I hope You can understand me

 

[Michel Walsh]

Hi,



SELECT CompanyName, Department, COUNT(*)
FROM tableName
WHERE City IN('Paris', 'London')
GROUP BY CompanyName, Department

but will NOT mention that HP has nothing.



Hoping it may help,
Vanderghast, Access MVP

 

[Van T. Dinh]

Try something like:

***untested***

SELECT VTZero.[Company Name], VTZero.Department,
VTZero.ZeroCount + Nz(VTReal.SelectedCount, 0)

FROM
(
SELECT [Company Name], Department, 0 As ZeroCount
FROM YourTable
GROUP BY [Company Name], Department
) As VTZero

LEFT JOIN
(
SELECT [Company Name], Department, Count(*) As SelectedCount
FROM YourTable
WHERE City IN('Paris', 'London')
GROUP BY [Company Name], Department
) As VTReal

ON VTZero.[Company Name] = VTReal.[Company Name]
AND VTZero.Department = VTReal.Department

 

[123]

Thank you for your help and Reply
Put This not Help my display all record
Thank you

 

[123]

Thank you for your help and answer
When using this code my data base closed and i can't open query in desgin
view.
Thank you for your help