2. PHPRunner General questions
  3. [SOLVED]  MySQL query help
This topic is locked
[SOLVED]

 MySQL query help

 		2/27/2015 10:30:32 PM
PHPRunner General questions
D
 DealerModules authorDevClub member

I have a table called sales that includes these fields:

StockNo. Salesperson SalesType etc.

1234 tsmith new

1235 tsmith used

1236 djones. new

1237 tsmith new

and so on...
Looking to query so the output would be :

salesperson / count of new / count of used.
Problem I am having is once I filter the count of new, the count of used is not available because I already filtered them out.
Want to use the output for a stacked bar chart.
Ant direction appreciated.
PhpRunner 8 with MySQL.
T
 		taumic 2/28/2015

Hello,
perhaps in this way?



select salesperson, salestype, count(salestype) as quantity from test group by salesperson, salestype


Regards
Taumic
D
 		DealerModules authorDevClub member 2/28/2015



Hello,
perhaps in this way?



select salesperson, salestype, count(salestype) as quantity from test group by salesperson, salestype


Regards
Taumic


Thanks Taumic, closer but the output I was look for is:
Salesperson, count of new, count of used

TSmith, 25, 3

GJones, 12, 13

etc.
The output from your query is close, I am getting
Salesperson, SalesType

TSmith, New, 25

TSmith, Used, 3

GJones, New, 12

GJones, Used, 13

etc.
My attempt is to use them in a stackable horizontal chart.
Thanks.
lefty 2/28/2015

Or try in chart settings group by salesperson , then group by New (check count) , then group by Used ( check count ). Use aggregate group by clauses.
Select

StockNo,

Count(Salesperson) As countofsalesperson,

Count(SalesType)As countofsalestype

From TableName

Group by Salesperson, SalesType, Sotckno
Then in chart setting group by field use first salesperson then salestype.
D
 		DealerModules authorDevClub member 3/2/2015

Solved - I broke my table down into two different entry tables, New and Used so I didn't have to do subqueries.
Thanks for the feedback from other forum members, I appreciated comments and direction.
Paul