Is it possible to select multiple conditional counts across three tables in a single SQL query?

My SQL-fu is too weak for this, and I'm not even sure it's possible in a single SQL call.

Given I have the following tables:

PARTNER +----+--------+ | id | name | +----+--------+ | 1 | bloggs | | 2 | jones | PARTNER MANAGER +----+--------------+------+ | id | partner_id | name | +----+--------------+------+ | 1 | 1 | fred | | 2 | 2 | dave | COMPANY +----+--------------------+--------+----------+ | id | partner_manager_id | name | active | +----+--------------------+--------+----------+ | 1 | 1 | comp1 | true | | 2 | 1 | comp2 | false | | 3 | 2 | comp3 | true | | 4 | 2 | comp4 | true | | 5 | 2 | comp5 | true | | 6 | 2 | comp6 | true |

I'd like to output the following in a single SQL call:

+--------------+--------------------+----------------------+ | partner_name | n_active_companies | n_inactive_companies | +--------------+--------------------+----------------------+ | bloggs | 1 | 1 | | jones | 4 | 0 |

I can join the three tables using two LEFT JOINs but how I can aggregate the counts (with or without the WHERE clause) is eluding me.

Am I barking up the wrong tree, so to speak?

-------------Problems Reply------------

This gets you most of the way there:

SELECT
partner_manager_id,
SUM(CASE WHEN active THEN 1 ELSE 0 END) AS n_active_companies,
SUM(CASE WHEN active THEN 0 ELSE 1 END) AS n_inactive_companies
FROM COMPANY
GROUP BY partner_manager_id

The rest of your question is basically asking how to join this result to the remaining tables. As you point out, to do this use JOINs.

SELECT
PARTNER.name,
T1.n_active_companies,
T1.n_inactive_companies
FROM
PARTNER
LEFT JOIN PARTNER_MANAGER ON partner_id = PARTNER.id
LEFT JOIN
(
SELECT
partner_manager_id,
SUM(CASE WHEN active THEN 1 ELSE 0 END) AS n_active_companies,
SUM(CASE WHEN active THEN 0 ELSE 1 END) AS n_inactive_companies
FROM COMPANY
GROUP BY partner_manager_id
) T1
ON T1.partner_manager_id = PARTNER_MANAGER.id

select p.name "Partner Name"
, c1.cnt "n_active_companies"
, c2.cnt "n_inactive_companies"
from partner p
, (select partner_manager_id id, count(partner_manager_id) cnt from company where active = 'true' group by partner_manager_id) c1
, (select partner_manager_id id, count(partner_manager_id) cnt from company where active = 'false' group by partner_manager_id) c2
where c1.id = p.id
and c2.id = p.id

select p.name as 'partner_name',
sum(case when active then 1 else 0) as 'n_active_companies',
sum(case when active then 0 else 1) as 'n_inactive_companies'
from COMPANY c
join PARTNER_MANAGER pm on c.partner_manager_id = pm.id
join PARTNER p on pm.partner_id = p.id
group by p.name

Category:sql Views:0 Time:2010-11-24
Tags: sql

Related post

  • Get count of distinct groups in a single SQL query in Firebird 1.5? 2009-08-19

    I ran across the following in a stored procedure: for select 1 from scan_queue where ( date_time_locked is null ) and ( scan_seqno >= :varMinScanSeqno ) and ( scan_seqno <= :varMaxScanSeqno ) group by loan_id, collateral_id, insurance_id into v

  • How do I update multiple records in a table with a single MySQL query? 2009-12-14

    I would like to update multiple records in a MySQL table using a single query. Basically, this is a tasks table, which has assignments for different people on different dates. When these assignments are changed and submitted via the Online form there

  • selecting id and count(id) from table in mysql 2010-09-27

    I am trying to make a photo album system in php and mysql. I have this code for list all photo albums: SELECT albums.*, count(pictures.id) AS countpic FROM albums LEFT JOIN pictures ON (albums.id=pictures.album_id) GROUP BY albums.id ORDER BY albums.

  • select multiple tables in single sql query 2011-02-21

    The other threads about this didn't seem to help me. I want to select all the information from one table, but order them by a value in another table. SELECT message, date, ip, name, website, id FROM guestbook_message WHERE deleted = 0 AND date > D

  • select multiple items in a pivot table page field vba (without looping through each pivot item) 2014-02-17

    Hi, I have a large pivot table with a page field with hundreds of entries. I would like to select multiple items via vba without having to loop through each Pivot Item. I would like to deselect all items then select the items I want. All the code I h

  • How do I do multiple updates in a single SQL query? 2008-09-02

    Okay, here's the case: I have an SQL query like this: UPDATE foo SET flag=true WHERE id=? Now, I have a PHP array, which has a list of IDs. Is there a neat way of doing this, outside parsing something like: foreach($list as $item){ $querycondition =

  • Inserting multiple rows in a single SQL query? 2009-01-17

    This question already has an answer here: How do I insert multiple rows WITHOUT repeating the “INSERT INTO dbo.Blah” part of the statement? 12 answers I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id a

  • Searching Multiple Rows at a time through a single SQL query 2012-01-09

    I have a table whose data is in this manner. A B C --------- 0 6 2 0 3 4 1 0 2 1 1 4 I wrote a SQL query - select A from Table where (B = 6 and C = 2) AND (B = 3 and C = 4). Obviously it returned zero results since this query would search in the same

  • single sql query for multiple tables 2011-04-24

    i need some help with this sql query heres my mysql table structure: DOMAINS - id - domain_name KEYWORDS - id - keyword - keyword_slug PAGES - id - id_domain - id_keyword what i need is my sql query to give me results in this format: - domain_name --

  • Renaming a column name in an SQL SELECT statement, using AS, fails when used inside of a sql:query tag; why? JSTL 1.2 2011-11-08

    I have been bashing my head against a wall all morning trying to figure this one out. I'm upgrading a very old application and I'm having trouble with the queries in the JSP pages. (I know, I know...) I have this query within an <sql:query> tag

  • Index value of the item selected in the combo box is being passed to the SQL query 2013-11-16

    I am trying to work on a form. On selecting any item from the combo box and clicking on search button, it should be able to find the matching record from the table and display it. After trying many things, I have got it to work but not exactly the wa

  • Multiple counts within a single SQL query 2009-08-13

    I'm trying to get the count of documents within 4 specific sections using the following code: SELECT category.id , category.title , count(ts1.section_id) AS doc1 , count(ts2.section_id) AS doc2 , count(ts3.section_id) AS doc3 , count(ts4.section_id)

  • In Oracle, how do you select multiple values from a related table and store them in a single column? 2010-09-28

    I'm selecting columns from one table and would like to select all values of a column from a related table when the two tables have a matching value, separate them by commas, and display them in a single column with my results from table one. I'm fair

  • Reject row from select if condition exists in another table 2011-07-08

    I have two tables. Contacts table - contact_id, contact_email, contact_name, etc Opt out table - contact_email, scope of their opt out (event, company) So one contact maybe have multiple event opt-outs but what I really care about is if they have a c

  • MySQL select with condition on left join table returns incomplete result set 2011-08-07

    I have a database containing two tables: event and session (one event has multiple sessions). I am building a query where I want to list event names and their corresponding first session date and last session date so users can see a range. The issue

  • Selecting multiple sets of rows with a single sql query 2011-10-24

    I have a table of questions set out like so.. id | question | answer | syllabus | difficulty I want to create an SQL statement that selects 5 questions at random for each of the distinct syllabuses when the difficulty is easy. So if there are 4 sylla

  • Select multiple items which match mapping table more then once 2012-02-22

    I am trying to select questions which have a certain topic id. These matches are stored in a mapping table, but I cannot work out how to select questions that have two tags to them seeing as they are stored as different rows in the table! Any help, p

  • Mysql select multiple colomn from non related tables 2010-05-28

    I have two table in mysql named rootpath with only one field 'root' and savecatogory with fields brandid,categoryid,name.There is no relation between these two tables.Now how can retrieve root,brandid,categoryid using single query.Please help me. ---

  • How to select multiple rows from a column/table in mysql? 2011-04-07

    I'm using the following code: $con = mysql_connect("NOTGIVEN","NOTGIVEN","NOTGIVEN"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("user_live", $con); $result = mysql_query("SELECT * FROM user_new_post ORDER BY user_date

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.125 (s). 11 q(s)