Solved

SQL query to sub-total data

Posted on 2014-10-07
10
162 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
  • 5
  • 5
10 Comments
 
LVL 48

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 48

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 48

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 48

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 48

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

809 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