Mysql - Get All Records That Have More Than 1 Record for the Same Id

MYSQL - Get all records that have more than 1 record for the same id

The simplest method doesn't use a subquery:

SELECT DISTINCT response_set_id
FROM responses
GROUP BY response_set_id, question_id
HAVING COUNT(*) > 1;

This is one of the very, very few instances where select distinct is used (appropriately) with group by.

Check if multiple records exist, with the same id

Could this solve your problem?

SELECT chatId FROM chatParticipants WHERE chatId IN (
  SELECT chatId FROM chatParticipants WHERE pageId
  IN ('123','deb47fba9bc6936ed76e831578baa480') GROUP BY chatId HAVING COUNT(*) = 2
) GROUP BY chatId HAVING COUNT(*) = 2

Hope this helps.

SELECT data with multiple rows using the same id based on 3 columns

You are close. You can get what you want using group by and having:

SELECT customer_id
FROM Data
WHERE value = 0 AND question_id IN (1, 2, 3)
GROUP BY customer_id
HAVING COUNT(DISTINCT question_id) = 3;

The HAVING clause specifies that all three questions are answered -- and that the answers are 0.

MySQL; Make a single row if multiple rows have the same id

A simple GROUP_CONCAT example

SELECT id, GROUP_CONCAT(value SEPARATOR ';') FROM test GROUP BY id;

Group by will bring all of the records with the same ID together, the group_concat will add all of the values for the same grouping together.

MySQL: Select rows with more than one occurrence

select en.id, fp.blogid, count(*) as occurrences
from french.blog_pics fp, french.blog_news fn, english.blog_news en 
where fp.blogid = fn.id 
and en.title_fr = fn.title 
and fp.title != ''
group by en.id
having count(*) > 1

SQL Query - Get results from multiple rows with same ID

I'm not sure I completely understand the question here, but to answer this specific question :

How can I create SELECT-query to get: pers_id from Admin, stamp_opened
from Admin and stamp_closed from User?

you would need to use self-join as rightly mentioned by Conrad in his comment above. Here's a way you could do it:

SELECT          tAdmin.pers_id,
                tAdmin.stamp_opened AS starttime,
                tUser.stamp_closed AS endtime,

FROM            tickets tUser
INNER JOIN      tickets tAdmin ON tUser.ticketnumber = tAdmin.ticketNumber
WHERE           tAdmin.stamp_created = 0
AND             tUser.stamp_created <> 0
AND             tUser.ticketnumber = 54094

The aforementioned query will only fetch you results for this particular ticket number(54094). If you want your SQL query to fetch results for all ticket numbers, just remove the last condition from the WHERE clause:

SELECT          tAdmin.pers_id,
                tAdmin.stamp_opened AS starttime,
                tUser.stamp_closed AS endtime,

FROM            tickets tUser
INNER JOIN      tickets tAdmin ON tUser.ticketnumber = tAdmin.ticketNumber
WHERE           tAdmin.stamp_created = 0
AND             tUser.stamp_created <> 0

Lastly, I see you are trying to find the response time for the tickets. That is, the difference between the time when the ticket was created and when it was closed. You can do that as follows:

SELECT          tAdmin.pers_id,
                tAdmin.stamp_opened AS starttime,
                tUser.stamp_closed AS endtime,
                (tUser.stamp_closed - tUser.stamp_created) AS 'Current request Service Response Time'
FROM            tickets tUser
INNER JOIN      tickets tAdmin ON tUser.ticketnumber = tAdmin.ticketNumber
WHERE           tAdmin.stamp_created = 0
AND             tUser.stamp_created <> 0
AND             tUser.ticketnumber = 54094

Again, note that this for only the current record. If you want an average of all the records along with the current data, you could do this:

 SELECT         tAdmin.pers_id,
                tAdmin.stamp_opened as starttime,
                tUser.stamp_closed as endtime,
                (tUser.stamp_closed - tUser.stamp_created) AS 'Current request Service Response Time',
                (SELECT         (SUM(stamp_closed) - SUM(stamp_created))
                 FROM           Tickets) AS 'Average Service Response Time'
FROM            tickets tUser
INNER JOIN      tickets tAdmin ON tUser.ticketnumber = tAdmin.ticketNumber
WHERE           tAdmin.stamp_created = 0
AND             tUser.stamp_created <> 0
AND             tUser.ticketnumber = 54094

You can substitute the WHERE clause with whatever business rules you have (in your SQL) to uniquely distinguish between the user and the admin.

Hope this helps!!!

MySQL select 1 of multiple rows with same field based on where clause

You can do it this way:

select * from Table1 
where (AddressType='Billing') or
(AddressType='Shipping' and ID not in (select ID from Table1 where AddressType='Billing'))
order by ID

Explanation:

1st condition is to filter only Billing address types.

2nd condition is to filter Shipping address types which do not have Billing with the same ID.

Result in SQL Fiddle

---

Related Topics