1. 

   shem, Member

   The following MySQL query gives the output below (except the last line of output):
   

   SELECT
   
       `dealer_name` AS 'Dealer',
   
       SUM(CASE WHEN `account_status` = 'WITH_CUSTOMER' THEN 1 ELSE 0 END) AS 'WITH_CUSTOMER',
   
       SUM(CASE WHEN `account_status` = 'OUT_FOR_REPO' THEN 1 ELSE 0 END) AS 'OUT_FOR_REPO',
   
       SUM(CASE WHEN `account_status` = 'REPOSSESSED' THEN 1 ELSE 0 END) AS 'REPOSSESSED',
   
       SUM(CASE WHEN `account_status` IN ('WITH_CUSTOMER', 'OUT_FOR_REPO', 'REPOSSESSED') THEN 1 ELSE 0 END) AS 'Totals'
   
   FROM tmp_table_dealer_performance
   
   GROUP BY `dealer_name`;

   Dealer WTH_CUSTOMER OUT_FOR_REPO REPOSSESSED Totals Gas Automotive 3 0 1 4 Exclusive Automotive 3 0 0 3 Trust Auto 3 0 1 4 Any Credit Auto sales 0 1 0 1 In Power Motors 13 1 2 16 Great Deals Auto Sales 3 0 1 4 USA Auto Inc 5 0 1 6 DR Auto 2 0 1 3 Major Auto 0 0 0 0 ALL DEALERS 32 2 7 41

   How can I produce the same output using the myDBR crosstab feature?
2. 

   myDBR Team, Key Master

   You can do:

   select 'dbr.crosstab', 'acc_status';
   
   select 'dbr.sum', 'count';
   
   select 'dbr.hsum', 'count';
   
   SELECT
   
     dealer_name AS 'Dealer',
   
     account_status as 'Account Status[acc_status]',
   
     count(*) as '[count]'
   
   from tmp_table_dealer_performance
   
   where account_status` IN ('WITH_CUSTOMER', 'OUT_FOR_REPO', 'REPOSSESSED')
   
   group by dealer_name, account_status;
   
   

   --
   myDBR Team
3. 

   shem, Member

   Thank you very much - it worked.
   Since you did a great job on the above, I would like to ask the same question as above, but wondering if I can add a second database column into the crosstab (namely sub_status in addition to account_status)
   So the original query would be:

   SELECT
   `dealer_name` AS 'Dealer',
   SUM(CASE WHEN `account_status` = 'WITH_CUSTOMER' THEN 1 ELSE 0 END) AS 'WITH_CUSTOMER',
   SUM(CASE WHEN `account_status` = 'OUT_FOR_REPO' THEN 1 ELSE 0 END) AS 'OUT_FOR_REPO',
   SUM(CASE WHEN `account_status` = 'REPOSSESSED' THEN 1 ELSE 0 END) AS 'REPOSSESSED',
   SUM(CASE WHEN (`sub_status` IN ('PAID_OFF', 'INSURANCE-PAID_OFF')) THEN 1 ELSE 0 END) AS 'PAID_OFF',
   SUM(CASE WHEN (`sub_status` IN ('CHARGED_OFF', 'REPO-CHARGEDOFF', 'CHARGED_OFF_BANKRUPTCY')) THEN 1 ELSE 0 END) AS 'CHARGED_OFF',
   SUM(CASE WHEN `sub_status` = 'BUYBACK' THEN 1 ELSE 0 END) AS 'BUYBACK'
   FROM tmp_table_dealer_performance
   GROUP BY `dealer_name`;

   Also as an aside question:
   Can I ever use 'dbr.hsum' when not in a crosstab?
4. 

   myDBR Team, Key Master

   A cross-tabulation is a structure where you have repetitive groups under a cross-tabulation column. In your first example, you had the count for each account_status. You could have another data value based on the account_status.

   In the second example you have (or it looks like it) two repetitive groups: account_status and sub_status. As they are not related to eachother, the structure does not seem to be a crosstable. See the documentation for examples.

   Can I ever use 'dbr.hsum' when not in a crosstab?

   The horizontal sum (dbr.hsum), calculates the horizontal sum over the crosstab's data columns, so it has no use outside the crosstabs.

   --
   myDBR Team

Reply

You must log in to post.