Good day!

There is a request:

SELECT players.uname, itemtypes.it_name,count( playerittemtype.itemtype ) AS count FROM itemtypes, playerittemtype, players WHERE itemtypes.`it_id` = playerittemtype.`itemtype` AND players.`uid` = playerittemtype.`player` GROUP BY playerittemtype.`itemtype`

When prompted, the user name, item name, and item quantity are displayed. And here's the catch: the number of items displays the one in the table, and I need to display only the quantity of one item that is assigned to this user. Help advice?

    2 answers 2

    in general, this query cannot be executed, because it is syntactically incorrect - you cannot select fields for which there is no grouping

    specify which goal is to calculate the amount of playerittemtype.itemtype? then in the count () parameters put an asterisk

    • I need the number of items each user and not the total amount of items - yurik
    • one
      So the query that is given in the question does it work? I don’t understand how this is possible Try SELECT players.uname, itemtypes.it_name, count (*) AS count FROM itemtypes, playerittemtype, players .uname, itemtypes.it_name - renegator
    • Okay, thanks, I just made this request myself, but I left count ( playerittemtype . playerittemtype ), I just needed GROUP BY players.uname, itemtypes.it_name to write - yurik
     SELECT t_players.uname,t_itypes.it_name,COUNT(t_pit.itemtype) AS t_count FROM playerittemtype AS `t_pit` LEFT JOIN players AS `t_players` ON (players.uid=t_pit.player) LEFT JOIN itemtypes AS `t_itypes` ON (itemtypes.it_id=t_pit.itemtype) GROUP BY t_pit.itemtype

    Try through join.