Long threaded SQL Query including Counts...

[Arg81]

This may not be possible in one hit....

I want to have a grid on a form that splits my customer into it's sites, and then displays counts from another table.

Now...it sounds easy.

So far I can do the following;

SELECT     CustomerSite.CustomerSiteName, COUNT(DWR.DWRNumber) AS TotalDWR
FROM         CustomerSite INNER JOIN
                      DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID
GROUP BY CustomerSite.CustomerSiteName

That works well so I get a total count of all DWR's in the DWR table that match the CustomerSiteID.

...now what I want to do...
I have a StatusID field (where 1 = Active and 2 = Complete). I want to add these counts to the query.

For a count of Active DWRs

SELECT     CustomerSite.CustomerSiteName, COUNT(DWR.DWRNumber) AS ActiveDWR
FROM         CustomerSite INNER JOIN
                      DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID
WHERE DWR.StatusID = 1
GROUP BY CustomerSite.CustomerSiteName

For a count of Complete DWRs

SELECT     CustomerSite.CustomerSiteName, COUNT(DWR.DWRNumber) AS CompleteDWR
FROM         CustomerSite INNER JOIN
                      DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID
WHERE DWR.StatusID = 2
GROUP BY CustomerSite.CustomerSiteName

What I don't know how to do is put it all together.

What I want to achieve is something like;

CustomerSiteName ---- TotalDWR ---- ActiveDWR ---- CompleteDWR

CustomerSiteA ---------------10------------ 8----------------- 2
CustomerSiteB ---------------29------------ 9----------------- 20

etc etc.

Any advice greatly appriciated as always!

Regards,

 

[Arg81]

Bringing this one back now I've got time to sit down and work it out.

I quickly put together this SQL - I thought using a "case" would work in the sub-SELECT query and replace NULL with 0 but it doesn't seem to work.

SELECT     CustomerSite.CustomerSiteName, COUNT(DWR.DWRNumber) AS TotalDWR, SUM(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END) AS ActiveDWR, 
                      SUM(CASE WHEN StatusID = 2 THEN 1 ELSE 0 END) AS CompleteDWR, reqs_agg.TSR AS TotalRequest
FROM         CustomerSite LEFT OUTER JOIN
                      DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID LEFT OUTER JOIN
                          (SELECT     CustomerSiteID, CASE WHEN COUNT(RequestNumber) IS NULL THEN '0' ELSE COUNT(RequestNumber) END AS TSR
                            FROM          SampleRequest
                            GROUP BY CustomerSiteID) AS reqs_agg ON CustomerSite.CustomerSiteID = reqs_agg.CustomerSiteID
WHERE     (CustomerSite.CustomerID = @CustomerID)
GROUP BY CustomerSite.CustomerSiteName, reqs_agg.TSR

...Yes, I know the SQL is messy and that I need to indent it

 

[cjard]

In most database systems COUNT() returns 0 if you attempt to count a null, so the WHEN part is never going to fire..

Also, you put a string-zero, not a numerical zero in your SQL:

CASE WHEN ... THEN '0' ELSE 123


Do you also write vb like:
Dim i as Integer = "123"


Hmm. no.. so dont be sloppy with the type-casting of SQLs either..


-
Yup, it's a mess, and you do need to indent it, cause I flat out refuse to read it while it looks like a dog's dinner. Have some pride, man!

ps- i like to see keywords indented, and blocks indented separately..

SELECT
  block
FROM
  block
  INNER JOIN
  block
  ON
    block
WHERE
  block

The logic being all peer-level elements are indented equally: SELECT, WHERE, GROUP BY
On another level:
table names, joined to other table names, bracketed queries etc, form a block of data etc..

You dont have to adopt this style, but do adopt one, for your own sanity..

 

[Arg81]

Also, you put a string-zero, not a numerical zero in your SQL:

CASE WHEN ... THEN '0' ELSE 123


Do you also write vb like:
Dim i as Integer = "123"

Yeah I know, was a typo on my behalf while trying to figure this damn thing out..

However it still displays null instead of 0.
Is there any other way of doing this? It's causing me a headache for something that seems so simple

 

[Arg81]

Think I'm going to start thinking this through from stratch.

Just had a user request to implement the total products sent for each site as well, so I now need to figure out how to aggregate that into the query....


There are 4 tables I need to query: CustomerSite, DWR, SampleRequest and SystemProduct.

I want the outcome to be able to be viewed in a grid so that;

CustomerSiteName | ActiveDWR | CompleteDWR | TotalDWR | TotalRequests | ProductsSent


Now we got most of that sorted, except TotalRequests showed a null where instead I wanted it to show a 0.

ProductsSent should be a count of SysProductID from SystemProduct table where SystemProduct.RequestNumber = SampleRequest.RequestNumber.

I'm not too sure how to do this at what stage, do I aggregate the TotalRequests first, then aggregate the ProductsSent, so I can match the requestnumber here?

I can do this in different queries, but I'm trying to do it so I have 1 query and 1 simple grid showing that 1 (a rather complicated and annoying) query!!!

 

[cjard]

If you want to dump your database to file so I can attach it to a SQLServer here, then that would be good.. It's a lot easier for me to write a query if I have a working DB to hammer

Picture your query like a star or a line, with tables stemming from a source. Each join links in a table. The hub of the star is the "driving table" to which other joins are created LEFT OUTER. Youre aiming for a data block that doesnt repeat rows so any 1:M relationships must be flattened before they are joined if you intend to perform summing. Pencil and paper, baby!!

 

[Arg81]

OK will try and dump to a file and pm you it later.

Sat here now with pen and paper

As I say, I can create a query for each individual stage I'm trying to do....the only other thing I can think of is to merge the data in a grid on the CustomerSiteID field....not sure on that though :/

 

[cjard]

OK will try and dump to a file and pm you it later.

Sat here now with pen and paper

As I say, I can create a query for each individual stage I'm trying to do....the only other thing I can think of is to merge the data in a grid on the CustomerSiteID field....not sure on that though :/

If you can create a query for each stage, then you can certainly do:

SELECT
  hub.siteID,
  stage1.stage1Val,
  stage2.stage2Val
FROM
  hubTableWheresiteIDIsUnique hub
  LEFT OUTER JOIN
  ( stage 1 sql) as stage1
  ON hub.ID = stage1.stage1ID
 
  LEFT OUTER JOIN
  ( stage 2sql) as stage2
  ON hub.ID = stage2.stage2ID

The clever bit is knowing which stages can be combined with tricks to improve performance

 

[cjard]

ProductsSent should be a count of SysProductID from SystemProduct table where SystemProduct.RequestNumber = SampleRequest.RequestNumber.

Your DB contains no SystemProduct table or SysProductID column

I'm assuming you meant SystemSample.SystemProductID

 

[cjard]

The following SQL is an evolution of what we had:

SELECT
  CustomerSiteName, 
  Sum(TotalDWR) as TotalDWR,
  Sum(ActiveDWR) as ActiveDWR,
  Sum(CompleteDWR) as CompleteDWR,
  Sum(TotalRequest) as TotalRequest,
  Sum(TotalProduct) as TotalProduct
FROM
(
  SELECT     
    cs.CustomerSiteName, 
    COUNT(dw.DWRNumber) AS TotalDWR, 
    SUM(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END) AS ActiveDWR, 
    SUM(CASE WHEN StatusID = 2 THEN 1 ELSE 0 END) AS CompleteDWR, 
    ISNULL(reqs_agg.TotalSampleRequest, 0) AS TotalRequest,
    ISNULL(reqs_agg.TotalProductRequest, 0) AS TotalProduct
  FROM 
    CustomerSite cs
 
    LEFT OUTER JOIN
    DWR dw 
    ON 
      cs.CustomerSiteID = dw.CustomerSiteID 
 
    LEFT OUTER JOIN
    (
      SELECT     
        CustomerSiteID, 
        COUNT(DISTINCT ss.RequestNumber) AS TotalSampleRequest,
        COUNT(SystemProdID) as TotalProductRequest
      FROM
        SampleRequest sr
        LEFT OUTER JOIN
        SystemSample ss
        ON
          ss.RequestNumber = sr.RequestNumber
		
      GROUP BY 
        CustomerSiteID
    ) reqs_agg 
    ON 
      reqs_agg.CustomerSiteID = cs.CustomerSiteID

 
  WHERE
    cs.CustomerID = 115
  GROUP BY 
    cs.CustomerSiteName, 
    reqs_agg.TotalSampleRequest,
    reqs_agg.TotalProductRequest
) dumbo
GROUP BY CustomerSiteName WITH ROLLUP

The following SQL is a rewrite to make it simpler looking:

SELECT
  CustomerSiteName, 
  Sum(TotalDWR) as TotalDWR,
  Sum(ActiveDWR) as ActiveDWR,
  Sum(CompleteDWR) as CompleteDWR,
  Sum(TotalRequest) as TotalRequest,
  Sum(TotalProduct) as TotalProduct
FROM
(
  SELECT     
    cs.CustomerSiteName, 
    COUNT(DISTINCT dw.DWRNumber) AS TotalDWR, 
    COUNT(DISTINCT CASE WHEN StatusID = 1 THEN dw.DWRNumber END) AS ActiveDWR, 
    COUNT(DISTINCT CASE WHEN StatusID = 2 THEN dw.DWRNumber END) AS CompleteDWR,     
    COUNT(DISTINCT ss.RequestNumber) AS TotalRequest,
    COUNT(DISTINCT SystemProdID) as TotalProduct    

  FROM 
    CustomerSite cs
 
    LEFT OUTER JOIN
    DWR dw 
    ON 
      cs.CustomerSiteID = dw.CustomerSiteID 
 
    LEFT OUTER JOIN
    SampleRequest sr
    ON
      sr.CustomerSiteID = cs.CustomerSiteID

    LEFT OUTER JOIN
    SystemSample ss
    ON
      ss.RequestNumber = sr.RequestNumber
 
  WHERE
    cs.CustomerID = 115
  GROUP BY 
    cs.CustomerSiteName
) dumbo
GROUP BY CustomerSiteName WITH ROLLUP

at the expense of more than doubling the execution time.

Execution time can be improved by:

Moving WHERE clauses as far inward as possible to limit the source rows
Reducing cartesian joins as much as possible by flattening results before joining

The slow query is particularly slow because it carteses a lot and then distinct hashing is used to bring about sensible results; Essentially you ask the database to prepare, say, 1 million rows, then only count some of them. The faster query has the database produce a few hundreds rows and gather aggregates from most of them

 

[! ! ! Aayannna]

If you can create a query for each stage, then you can certainly do:

SELECT
  hub.siteID,
  stage1.stage1Val,
  stage2.stage2Val
FROM
  hubTableWheresiteIDIsUnique hub
  LEFT OUTER JOIN
  ( stage 1 sql) as stage1
  ON hub.ID = stage1.stage1ID
 
  LEFT OUTER JOIN
  ( stage 2sql) as stage2
  ON hub.ID = stage2.stage2ID

thx, is very helpfully

 

[Arg81]

Solved

In theory I don't need to use WITH ROLLUP, I removed this and the query did seem to run quicker. I used the top query instead of the bottom one.

Thanks a bunch for sorting that out. Looking at it now I fully understand it, and I can see where I was going wrong getting the total products fitted in to the equation.

PS - what are you trying to say RE dumbo

PPS - would appriciate it if you could fully delete the data I sent you

This can finally be marked as solved!!!

 

[cjard]

database has been dropped from email, disk and sqls. till the next time..

dumbo was just some dumb name for the block. I dont often work with SQLS and i thought that "Syntax Error Near GROUP on line 16" was because it thought i was trying to alias the block with "group".. i could have picked XYZ


Query design is an art, for sure

ROLLUP shouldnt cost much, because its the last thing to be done on a very small result set - 10 rows that need rolling up should be rolled in microseconds..