SQL Select Help

[Dominik]

Hello,

 

i am starting to freak out ... i am trying to make a select but it dont want to work.

 

So what do i have:

 

i have 2 Tables

 

table 1: customer (contains the name) (fieldname: name)

table 2: bookings (contains the name from customer) (fieldname: belegung)

 

in table 2 there are multiple lines with lets say 10 customer xyz and 3 customer xxx

in table 1 every customer is only once and it includes 5 customer

 

my output i want to have now is a group by where i have the customer in column 1 and the count(*) in column 2

 

so in my case

 

customer xyz - 10

customer xxx - 3

customer 3     - 0

customer 4 - 0

customer 5 - 0

 

i only manage to group them, so i can see it like

customer xyz - 10

customer xxx - 3

customer 3     - 1

customer 4 - 1

customer 5 - 1

 

as it goes on the count(*) from table 1

 

please if someone could help me i would be pleased

[Ch0cu3r]

Could try

SELECT c.name, COUNT( b.belegung ) AS bookings
FROM customer c
LEFT JOIN bookings AS b ON c.name = b.belegung
GROUP BY c.name

Results

+-----------+----------+
|   name    | bookings |
+-----------+----------+
| Customer1 | 3        |
| Customer2 | 2        |
| Customer3 | 1        |
| Customer4 | 0        |
| Customer5 | 0        |
+-----------+----------+

Ideally you should have the id of the customer recorded in your bookings table and not their name.

[Dominik]

yeah thx it works - i used count(*) - else my statement was the same .... ahhh 

 

anyway thx again ^^