Solved

SQL query to sub-total data

Posted on 2014-10-07
10
154 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 45

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 45

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
 

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 45

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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 45

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 45

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now