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...
1:1
relation? (One Agent, one Product): Then you dont need the agentdata-table at all.1:n
relation? (One Agent, multipe Products): Then you don't need the ProductName
in the data-table.n:1
relation? (Multiple Agents, One Product): Then you don't need the agentid
in the agentdata-table.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.