计算1列中的项目并将它们分成两列

I have a two tables, data and agentdata.

data (agentid, SimCardNumber, ProductName, Agentemail)

agentdata (id, agentid, ProductName)

currently i deal only two products and i want to view this list as group by agentemail

agentemail    |  Product1  |  Total1   |  Product2  |  Total2
-----------------------------------------------------------
abc@gmail.com |  ball      |   5       |  bat       |  0

i tried this query but i have problem to show two products and their count side by side.

SELECT a.agentid,  d.Agentemail, a.ProductName, COUNT(a.ProductName) AS Total  
FROM  data AS d, agentdata AS a 
WHERE d.agentid=a.agentid
GROUP BY a.ProductName

SQL isn't built to handle side-by-side things like that. It works on rows, you want it to display columns.

Your original query should GROUP BY agentid, ProductName to separate out the agents from the product totals.

If you know that you only have two product and no more, you can write a query to display just those two products side-by-side. If you add a third product, it will not include it.

SELECT a.agentid,  d.Agentemail, 
    'ball' AS Product1, 
    SUM(IF(a.ProductName = 'ball', 1, 0)) AS Total1,  
    'bat' AS Product2, 
    SUM(IF(a.ProductName = 'bat', 1, 0)) AS Total2,  
FROM  data AS d, agentdata AS a 
WHERE d.agentid=a.agentid
GROUP BY agentid

This sort of query is OK for doing quick checks on data, but your front-end should use a more solid query and build the table there.

data (agentid, SimCardNumber, ProductName, Agentemail)

agentdata (id, agentid, ProductName)

You should REALLY think about your table layout. if agentid == agentid and ProductName == ProductName, this layout is against everything a normalized database would store - and therefore no queries that actually make sence are possible...

  • do you want a 1:1 relation? (One Agent, one Product): Then you dont need the agentdata-table at all.
  • do you want a 1:n relation? (One Agent, multipe Products): Then you don't need the ProductName in the data-table.
  • do you want a n:1 relation? (Multiple Agents, One Product): Then you don't need the agentid in the agentdata-table.
  • do you want a n:n relation? (Multiple Agents, Multiple Products): Then you need to remove ProductName from the data-table, remove agentid from the agentdata-table and create a Mapping Table with ProductName and agentid on which you finally could join your data- and agentdata-table.

With this layout - and without knowing what data is stored where, it's impossible to give an answer that really helps you with your problem.