Grouping simiar records to one

Question

I'm working on a social project and there are some tables which will have similar records which i'd like to group them into one. but with my query in brings all the records instead of grouping them into one.

SELECT 
    users_pics.email
    , pic = MIN(pic)
    , count(pic) AS total
    ,fname
    ,lname
    ,profile_pix
    ,RTRIM(wardrobe) as wardrobe
    ,gender
    , resp_email

FROM 
    users_pics  
INNER JOIN 
    profile 
ON 
    users_pics.email = profile.email, dbo.viva_friends

WHERE 
    users_pics.stat = 'new'  
AND  
    resp_email = MMColParam2  
OR   
    req_email= MMColParam 
AND 
    Status = 'Yes '
GROUP BY 
    users_pics.email
    ,fname,lname
    ,profile_pix
    ,wardrobe
    ,gender
    ,req_email
    ,resp_email

ORDER BY 
    MAX(users_pics.u_pic_id) DESC

please is there a way out. when i hit on the run button i get this.

enter image description here

Please show us your input data and expected results for this query. Also, which RDBMS are you using ? — P. Camilleri, May 14 '14 at 08:09
For me the question is not so clear, please can you show some example of the data and which output you want to have. — OliC, May 14 '14 at 08:11
what i want it to bring is to group the results with similar emails from both tables instead of showing them all one by one. — blay, May 14 '14 at 08:13
Dont put that in comments, edit your post. And please show expected results __in the form of a table__ — P. Camilleri, May 14 '14 at 08:14
Added `sql-server` tag based on the proprietary column alias syntax. — , May 14 '14 at 08:16
@M.Massias: to my knowledge SQL Server is the only DBMS that allows to define an alias using this strange syntax: `pic = MIN(pic)`. — , May 14 '14 at 08:20
possible duplicate of [How to make a query with group\_concat in sql server](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — P. Camilleri, May 14 '14 at 08:23

score 0 · Accepted Answer · answered May 14 '14 at 09:30

Looks like you just want a simple GROUP BY;

SELECT users_pics.email, MIN(pic) AS pic, COUNT(pic) AS total, MAX(fname) AS fname,
       MAX(lname) AS lname, MAX(profile_pix) AS profile_pic, 
       RTRIM(MAX(wardrobe)) AS wardrobe, MAX(gender) AS gender, MAX(resp_email) resp_email
FROM users_pics  
INNER JOIN profile 
ON users_pics.email = profile.email, 
     dbo.viva_friends
WHERE (users_pics.stat = 'new' AND resp_email = MMColParam2)
   OR (req_email= MMColParam AND Status = 'Yes ')
GROUP BY 
    users_pics.email

Since I can't see your table structure, I can't tell what dbo.viva_friends is used for. It looks like it may be possible to just remove.

Grouping simiar records to one

1 Answers1