?
Solved

SQL query to sub-total data

Posted on 2014-10-07
10
Medium Priority
?
179 Views
Last Modified: 2014-10-26
I have a very basic understanding of SQL and need to modify a query which someone else created.  The current query shows the number of clients added to our database within a time window.  We now want to sub-total this query by branch, by quarter.

The current query is this:

declare     @start_date datetime

      select      @start_date = dateadd(mm,-24,getdate())

      select
            @start_date                       as period_desc,
            office_name,
            c.client_org_name                 as parent_company,
            a.client_org_name                 as company,
            a.address1                        as address1,
            a.address2                        as address2,
            a.city                            as city,
            a.pincode                         as pincode,
            a.state                           as state,
            a.contact_first_name              as contact_first_name,
            a.contact_last_name               as contact_last_name,
            a.contact_phone                   as contact_phone,
            a.client_id                       as client_id,
            a.created_on                      as created_on,
            isnull(count(d.group_id),0)         as number_of_orders

      from  client_organization     a,
            branch_offices           b,
            client_organization     c,
            orders                  d

      where b.office_id =  a.office_id
      and   c.group_id  =  a.client_id
      and   d.group_id  =* a.group_id
      and   a.created_on >= @start_date

      group by office_name, c.client_org_name, a.client_org_name, a.address1, a.address2, a.city, a.pincode, a.state,
             a.contact_first_name, a.contact_last_name, a.contact_phone, a.client_id, a.created_on

      order by office_name, parent_company, company


Can anyone assist in breaking the details down to sub-total this, as requested.

Any assistance is appreciated.
0
Comment
Question by:DrakeCA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40366218
If the field CREATEDON is date data type than this should work:
declare     @start_date datetime

select      @start_date = dateadd(mm,-24,getdate())

select
     @start_date                       as period_desc,
     office_name,
     c.client_org_name                 as parent_company,
     a.client_org_name                 as company,
     a.address1                        as address1,
     a.address2                        as address2,
     a.city                            as city,
     a.pincode                         as pincode,
     a.state                           as state,
     a.contact_first_name              as contact_first_name,
     a.contact_last_name               as contact_last_name,
     a.contact_phone                   as contact_phone,
     a.client_id                       as client_id,
     DATEPART (QUARTER, a.created_on)  as created_on,
     isnull(count(d.group_id),0)       as number_of_orders
from  client_organization     a,
     branch_offices           b,
     client_organization     c,
     orders                  d
where b.office_id =  a.office_id
and   c.group_id  =  a.client_id
and   d.group_id  =* a.group_id
and   a.created_on >= @start_date
group by office_name, c.client_org_name, a.client_org_name, a.address1, a.address2, a.city, a.pincode, a.state,
      a.contact_first_name, a.contact_last_name, a.contact_phone, a.client_id
order by office_name, parent_company, company

Open in new window

0
 

Author Comment

by:DrakeCA
ID: 40366245
This tells me the quarter the order was created on, but doesn't provide any sub-totals per branch, per quarter.

How do I get that additional information?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40366266
Do you need to present all those columns? If not you can reduce It only to those one that really matters. Like this:
declare     @start_date datetime

select      @start_date = dateadd(mm,-24,getdate())

select
     @start_date                       as period_desc,
     office_name,
     DATEPART (QUARTER, a.created_on)  as created_on,
     isnull(count(d.group_id),0)       as number_of_orders
from  client_organization     a,
     branch_offices           b,
     client_organization     c,
     orders                  d
where b.office_id =  a.office_id
and   c.group_id  =  a.client_id
and   d.group_id  =* a.group_id
and   a.created_on >= @start_date
group by office_name
order by office_name

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:DrakeCA
ID: 40366322
Sure, we can minimize the report to only those fields, just to pull this particular information.

How would I subtotal it by branch, per quarter then?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40366334
Isn't the number of orders that you want (it's the last column in the SELECT fields)?
If not just replace it for the field that you want. And the operation also (COUNT or SUM)?
declare     @start_date datetime

select      @start_date = dateadd(mm,-24,getdate())

select
     @start_date                       as period_desc,
     office_name,
     DATEPART (QUARTER, a.created_on)  as created_on,
     count(d.group_id)       as number_of_orders
from  client_organization     a,
     branch_offices           b,
     client_organization     c,
     orders                  d
where b.office_id =  a.office_id
and   c.group_id  =  a.client_id
and   d.group_id  =* a.group_id
and   a.created_on >= @start_date
group by office_name
order by office_name

Open in new window

0
 

Author Comment

by:DrakeCA
ID: 40366428
I'm not looking for the total number of orders.  I'm trying to determine the total number of clients added to the system.  Technically every client should have a unique ID number.  I assume I would count based on that ID number?

When I post the query you have listed I get an error stating "Msg 8120, Level 16, State 1, Line 5
Column 'client_organization.created_on' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."


How do I get rid of that error?  I tried adding "created_on" to the group_by line, but it tells me it's an ambiguous column name.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40366450
Ok. Now we have something. Try this one:
declare     @start_date datetime

select      @start_date = dateadd(mm,-24,getdate())

select
     @start_date                       as period_desc,
     office_name,
     DATEPART (QUARTER, a.created_on)  as created_on,
     count(a.client_id)       as number_of_clients
from  client_organization     a,
     branch_offices           b,
     client_organization     c,
     orders                  d
where b.office_id =  a.office_id
and   c.group_id  =  a.client_id
and   d.group_id  =* a.group_id
and   a.created_on >= @start_date
group by office_name, DATEPART (QUARTER, a.created_on)
order by office_name

Open in new window

0
 

Accepted Solution

by:
DrakeCA earned 0 total points
ID: 40395713
I ended up using the query below:

use olo_na

declare     @start_date datetime
select      @start_date = dateadd(mm,-24,getdate())

SELECT office_name, DATEPART(YEAR,a.created_on) [Year],
DATEPART(QUARTER,a.created_on) [Quarter],
COUNT(1) [created_on]
FROM client_organization a
left join branch_offices  b on b.office_id =  a.office_id
where a.created_on >= @start_date
GROUP BY office_name, DATEPART(YEAR,a.created_on),DATEPART(QUARTER,a.created_on)
ORDER BY 1,2,3
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40396289
Even I didn't give you the solution, maybe for lack of information from you, your solution was based on mine.
0
 

Author Closing Comment

by:DrakeCA
ID: 40404575
This is the solution
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question