CODEWITHSUNDEEP
mysqlsqljoin
LittleLebowski
LittleLebowski

Reputation: 7941

How to write a join query in MySql?

I've a database with the following schema:

id: int

name: varchar

email: varchar

facebook: varchar (possible values: Y or N)

twitter: varchar (possible values: Y or N)

time: timestamp (create timestamp)

We can have multiple rows for the same person where facebook, twitter and time may/will vary. I want to make a query where I finally want the following:

name

email

total_count (total number of rows for email)

total_facebook_yes (Total number of YES for Facebook for email)

total_twitter_yes (Total number of YES for Twitter for email)

last_time (The timestamp from the latest row for email)

I'm stuck at writing a query. Can anyone guide me. Please help. :(

Upvotes: 0

Views: 86

Answers (2)

aweis
aweis

Reputation: 5596

You don't need a join query, you need an group with count and max functions.

Something like:

select
    name,
    email,
    count(name) as total_count,
    sum(facebook = 'y') as total_facebook_yes,
    sum(case when twitter = 'y' then 1 else 0 end) as total_twitter_yes,
    max(time) as last_time
from `table_name`
group by name, email

With the limited knowledge about the schema, i would suggest you to look into splitting the table into multiple tables with relations. Have a look at wiki 3th-normal-form to start with.

Upvotes: 2

bonCodigo
bonCodigo

Reputation: 14361

Looking at the expected results, where do you expect the JOIN to take place? Try this. Mysql is forgiving in terms of boolean wrapping, 

SELECT NAME, EMAIL, COUNT(FACEBOOK='yes') as FBTOTAL_Count, COUNT(Twitter='yes') as TOTALTW_Count
FROM YOURTABLE
GROUP BY FACEBOOK, TWITTER, NAME, EMAIL

Upvotes: 0

Related Questions