Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    19

    Unanswered: Multiple Count functions, with different criteria from an alias table

    I am trying to develop a query to run across multiple tables, then run multiple count functions with specific criteria for each. It seemed simplest to first pull all the data into an alias table, then use this table to pull all the date from (which may not be the simplest way, however I am limited in my knowledge).

    For a simple example I have tables, user, shop, staff, shop_staff

    user
    id | name
    =========
    1 | John
    2 | Kate
    3 | Lisa

    shop
    id | store |assigned_user_id
    =========================
    S1 | Coffee Shop | 1
    S2 | Pizza Shop | 2
    S3 | Restaurant | 3
    S4 | Take Away | 1

    staff
    id | trainer
    =========
    1 | Will
    2 | Ben
    3 | Jack

    shop_staff
    id | storeid | trainerid
    ====================
    A | S1 | 1
    A | S2 | 1
    A | S2 | 2
    A | S3 | 3
    A | S4 | 3

    I pull all the data required into an alias table, Alt, using this segment of in the final query to give the resulting table below.

    Code:
    SELECT     shop.id   ,
               shop.store,
               user.name ,
               staff.trainer
    FROM       user
               INNER JOIN shop
               ON         user.id = shop.assigned_user_id
               INNER JOIN shop_trainer
               ON         shop.id = shop_staff.storeid
               INNER JOIN trainer
               ON         shop_staff.trainerid = staff.id

    id | store | name | trainer
    ================================
    S1 | Coffee Shop | John | Will
    S2 | Pizza Shop | Kate | Will
    S2 | Pizza Shop | Kate | Ben
    S3 | Restaurant | Lisa | Jack
    S4 | Take Away | John | Jack

    I then use the following code to display a list of the total number stores and trainers a user has linked to them. Stores must be distinct, and stores and trainers have some criteria to the search.

    Code:
    SELECT DISTINCT Alt.name AS name          ,
                    COUNT (Alt.trainer) AS trainer,
                    COUNT(DISTINCT Alt.store) AS store
    FROM            (SELECT    shop.id   ,
                               shop.store,
                               user.name ,
                               staff.trainer
                    FROM       user
                               INNER JOIN shop
                               ON         user.id = shop.assigned_user_id
                               INNER JOIN shop_trainer
                               ON         shop.id = shop_staff.storeid
                               INNER JOIN trainer
                               ON         shop.staff_trainerid = trainer.id
                    )
                    AS Alt
    WHERE           Alt.store IN ('Coffee Shop',
                                    'Pizza Shop' ,
                                    'Take Away')
    AND             Alt.trainer IN ('Will',
                                      'Ben')
    GROUP BY        Alt.name
    name | trainer | store
    =======================
    John | 1 | 2
    Kate | 2 | 1
    Lisa | NULL | NULL

    The result I want is above, however obviously this does not occur as the two criteria statements need to both be filled in the code I wrote.

    What I want to do is have the first criteria only apply to the store count, and the second only apply to the trainer count.

    Unsure how to do this. Hopefully that all makes sense. Thanks for any help!
    Last edited by BridgeCat; 01-06-13 at 22:15. Reason: Errors in sample tables not consistent with queries

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) Your queries are not consistent with your sample tables.

    (2) Even if I changed shop_trainer and trainer in your first query by shop_staff and staff, like...
    Code:
    SELECT     shop.id   ,
               shop.store,
               user.name ,
               staff.trainer
    FROM       user
               INNER JOIN shop
               ON         user.id = shop.assigned_user_id
               INNER JOIN shop_staff
               ON         shop.id = shop_staff.storeid
               INNER JOIN staff
    /* I also changed this ON condition
               ON         shop.staff_trainerid = trainer.id
     to the following */
               ON         shop_staff.trainerid = staff.id
    ;
    the results of the query may be different from your shown resulting table.
    Code:
    id | store       | name | trainer
    ================================
    S1 | Coffee Shop | John | Will   
    S2 | Pizza Shop  | Kate | Will   
    S2 | Pizza Shop  | Kate | Ben    
    S3 | Restaurant  | Lisa | Jack   
    S4 | Take Away   | John | Jack
    Last edited by tonkuma; 01-06-13 at 02:42.

  3. #3
    Join Date
    Jun 2012
    Posts
    19
    Hi Tonkuma,

    Thanks for the reply! I made quite a mess of that example. Needless to say I discovered CASE statements and was able to write the query I needed anyway after a few hours searching dbforums threads!

    Thanks

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you found a solution,
    please publish your query.
    It will help others to undersand and lean SQL technique more.

  5. #5
    Join Date
    Jun 2012
    Posts
    19
    Had to make sure it work first before throwing it up! I edited the original post so it was consistent.

    Code:
    SELECT DISTINCT Alt.name           ,
                    COUNT (CASE WHEN Alt.store IN ('Coffee Shop',
                                    'Pizza Shop' ,
                                    'Take Away') THEN 1 ELSE 0 END) AS store,
                    COUNT(DISTINCT CASE WHEN Alt.trainer IN ('Will',
                                      'Ben') THEN 1 ELSE 0 END) AS trainer
    FROM            (SELECT    shop.id   ,
                               shop.store,
                               user.name ,
                               staff.trainer
                    FROM       USER
                               INNER JOIN shop
                               ON         USER.id = shop.assigned_user_id
                               INNER JOIN shop_trainer
                               ON         shop.id = shop_staff.storeid
                               INNER JOIN trainer
                               ON         shop_staff.trainerid = staff.id
                    )
                    AS Alt
    GROUP BY        Alt.name
    So instead of using WHERE & AND statements at the end of the query that apply to the whole output, I used CASE statements to define the count, and assign values to the true and false.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •