Select Query from postgres with count

Question

I have two tables, one contains customers, the other one the bookings.
Now i want to see how many bookings come from one person but display it with their name instead of the id.

SELECT booking.id, COUNT(booking.id) AS idcount
FROM booking
GROUP BY booking.id ORDER BY idcount DESC;

The output is (correct count):

       id | idcount 
----------+--------
        2 |      8
        1 |      4

My attempt at getting the name displayed instead of the id was:

SELECT customer.lastn, customer.firstn, COUNT(booking.id) AS idcount
FROM booking, customer
GROUP BY customer.lastn, customer.firstn ORDER BY idcount DESC;

The output (wrong count):

 lastn    | firstn  | idcount 
----------+---------+--------
 Adam     | Michael |     13
 Jackson  | Leo     |     13

13 is the total number of bookings (i just cut the output off) so there's that coming from, however i cant make the transition to get the right count with the name.

why you are using cross join between booking and customer ? is booking.id equal to customer.id ? — Fahad Anjum
– Fahad Anjum, Commented May 17, 2017 at 18:47
Yes, the id in booking is one part of the primary key and equal to the customer id — AnnoyedGuy
– AnnoyedGuy, Commented May 17, 2017 at 18:49

JNevill · Accepted Answer · 2017-05-17 18:53:50Z

1

You need to use a JOIN in your FROM clause:

SELECT customer.lastn, customer.firstn, COUNT(booking.id) AS idcount
FROM booking
    JOIN customer ON booking.id = customer.id
GROUP BY customer.lastn, customer.firstn 
ORDER BY idcount DESC;

The JOIN here tells how the booking table relates to your customer table.

edited May 17, 2017 at 18:53

answered May 17, 2017 at 18:48

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AnnoyedGuy Over a year ago

Indeed, i tried JOIN but messed the syntax up, thanks

Fahad Anjum Over a year ago

There might be possibility of having same last name and first name for two different customer. In that case it will sum those customer count.

JNevill Over a year ago

@FahadAnjum agreed. @annoyedguy, to get around that you do a GROUP BY customer.id, customer.lastnm, customer.firstn

AnnoyedGuy Over a year ago

Thats luckily not the case yet but i guess that might save me some headache in the future!

Fahad Anjum Over a year ago

@AnnoyedGuy Yes.

Collectives™ on Stack Overflow

Select Query from postgres with count

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related