WHERE SQL functions

Asked

Viewed 62 times

0

I have the following query:

$qrysel = "select * from won_auctions w left join registration r on w.userid=r.id where xxx;

This query will show a list of users of the table won_auctions and take the user name in the table Registration.

The idea is that it checks how many records each user has in the table won_auctions during the current month, and the total.

I also need to sort to display those that have more records for the first, for the current month.

2 answers

1


SELECT
    COUNT(*) AS qtd_reg,
    w.*,
    r.*
FROM
    won_auctions w
LEFT JOIN registration r ON w.userid = r.id
WHERE
    YEAR([campo_data]) = '2017' -- ANO DO FILTRO
    AND MONTH([campo_data]) = '11' -- MES DO FILTRO;
GROUP BY [user_id]
ORDER BY qtd_reg

0

This is an option, as you did not give many details about your architecture or how are your tables, I used generic names:

select * from (
 select r.username, count(w.registros) as contagem
 from won_auctions w 
 left join registration r 
  on w.userid=r.id where data >= 'inicio do mes' and data <= 'final do mes'
 group by r.userid
) a
order by a.contagem desc
  • 1

    Good morning! You have skype for us to talk about this problem?

Browser other questions tagged

You are not signed in. Login or sign up in order to post.