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]

Wouldn't the 3 "totals" then appear on every row? ... I mean if it does that's not an issue, the most sites for 1 customer is about 8.

^^ thats the point I was thinking it was easier for the program to do it than trying to SQL it

Screenshot of my totals for a customer (sites hidden)
As you can see, if the Total Sample Requests is null, it doesn't display a value, that is what was making the "totalling code" bomb out.

I'll have a go at trying to SQL wrap. I've never needed to go this far in depth with queries so I'll see how I get on!!!!

 

[cjard]

Wouldn't the 3 "totals" then appear on every row?

Um.. no?

Remembering the sales example i gave, I grouped by country, and also by country, state, store

I got 2 rows for country, state, store, then one row for country..

You would group by a grouping set that uses one common element for all rows

If there is no common element you can group by an empty set:


If i'd done:

SELECT country, state, store, sum(sale_amount) FROM sales GROUP BY GROUPING SETS (country, state, store),(country),( )


its like:
SELECT country, state, store, sum(sale_amount) FROM sales GROUP BY country, state, store
UNION
SELECT country, null, null, sum(sale_amount) FROM sales GROUP BY country
SELECT null, null, null, sum(sale_amount) FROM sales

 

[Arg81]

Right, the SQL I'm currently using is;

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.TotalSampleRequest AS TotalRequest
FROM         CustomerSite LEFT OUTER JOIN
                      DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID LEFT OUTER JOIN
                          (SELECT     CustomerSiteID, COUNT(RequestNumber) AS TotalSampleRequest
                            FROM          SampleRequest
                            GROUP BY CustomerSiteID) AS reqs_agg ON CustomerSite.CustomerSiteID = reqs_agg.CustomerSiteID
WHERE     (CustomerSite.CustomerID = @CustomerID)
GROUP BY CustomerSite.CustomerSiteName, reqs_agg.TotalSampleRequest

Am I right in thinking that I then add a SELECT statement to the top of this - maybe;

SELECT sum(TotalDWR), sum(ActiveDWR), sum(CompleteDWR), sum(TotalRequest)
FROM
 
    [COLOR=seagreen]'SO THE CODE ABOVE GOES HERE?????[/COLOR]
 
[COLOR=red]..Then what goes here? Do I replace GROUP BY CustomerSite.CustomerSiteName, reqs_agg.TotalSampleRequest with GROUP BY GROUPING SET CustomerSite.CustomerSiteName, reqs_agg.TotalSampleRequest??[/COLOR]

 

[cjard]

Take your code:

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.TotalSampleRequest AS TotalRequest
FROM         CustomerSite LEFT OUTER JOIN
                      DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID LEFT OUTER JOIN
                          (SELECT     CustomerSiteID, COUNT(RequestNumber) AS TotalSampleRequest
                            FROM          SampleRequest
                            GROUP BY CustomerSiteID) AS reqs_agg ON CustomerSite.CustomerSiteID = reqs_agg.CustomerSiteID
WHERE     (CustomerSite.CustomerID = @CustomerID)
GROUP BY CustomerSite.CustomerSiteName, reqs_agg.TotalSampleRequest

and wrap it in this:

SELECT
  CustomerSiteName,
  Sum(ActiveDWR)
...
(
  <WRAPPED CODE GOES HERE>
)
GROUP BY GROUPING SETS((CustomerSiteName),( ))

 

[Arg81]

I understand what you are saying (I think), but it's erroring.

Code I'm using is;

SELECT
  CustomerSiteName, Sum(TotalDWR)
 
(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.TotalSampleRequest AS TotalRequest
FROM         CustomerSite LEFT OUTER JOIN
                      DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID LEFT OUTER JOIN
                          (SELECT     CustomerSiteID, COUNT(RequestNumber) AS TotalSampleRequest
                            FROM          SampleRequest
                            GROUP BY CustomerSiteID) AS reqs_agg ON CustomerSite.CustomerSiteID = reqs_agg.CustomerSiteID
WHERE     (CustomerSite.CustomerID = 115)
GROUP BY CustomerSite.CustomerSiteName, reqs_agg.TotalSampleRequest))
GROUP BY GROUPING SETS((CustomerSiteName),( ))

Error is

Error in list of function arguments: '(' not recognized.
Unable to parse query text.

Then on the next screen get the following error;

 

[cjard]

i missed a FROM

It was a deliberate mistake to test if you were listening



OK, heres a quick up down with DBs.

DBs select from blocks of data


A table is a block of data:
SELECT * FROM table


A view is a block of data:
SELECT * FROM view


A query is a block of data:
SELECT * FROM( SELECT * FROM table )


A complicated query is a block of data:
SELECT * FROM(
SELECT * FROM table1
UNION
SELECT * FROM table2
)


Nesting is possible:

SELECT * FROM( 
  SELECT * FROM( 
    SELECT * FROM (
      SELECT * FROM( SELECT * FROM table )
    )
  )
)

At each level, you only have available what you selected, or what is in the table if the block is a table:

SELECT a, b FROM table
SELECT a FROM( SELECT a, b FROM table )

 

[Arg81]

Actually I tried with a FROM

still had the same error though :/

 

[cjard]

ps, your SQL is a complete mess! Indentation is good for all code:

The format I adopt is like:

SELECT
  CustomerSiteName, 
  Sum(TotalDWR)
[b]FROM[/b]
(
  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, 
    reqs_agg.TotalSampleRequest AS TotalRequest
  FROM 
    CustomerSite cs
    
    LEFT OUTER JOIN
    DWR dw 
    ON 
      cs.CustomerSiteID = dw.CustomerSiteID 
      
    LEFT OUTER JOIN
    (
      SELECT     
        CustomerSiteID, 
        COUNT(RequestNumber) AS TotalSampleRequest
      FROM
        SampleRequest
      GROUP BY 
        CustomerSiteID
    ) reqs_agg 
    ON 
      reqs_agg.CustomerSiteID = cs.CustomerSiteID
     
  WHERE
    cs.CustomerID = 115
  GROUP BY 
    cs.CustomerSiteName, 
    reqs_agg.TotalSampleRequest
)
GROUP BY GROUPING SETS((CustomerSiteName),( ))

Note, when I reformmated this code I found 2 extraneous ) brackets

Indenting helps write clean code! Use it!

(I know the designer scraps the indenting, but you should always write your queries separate, in a query analyzer, then paste them into VS, so kleep them indented nicely!)

 

[Arg81]

Yeah I use to write them in Query Analyzer, then store them in case I needed again and can simply copy and paste.

However I then got myself into the programmer's lazy ways, you know, beard growth, long hair, lots of coffee, and letting VS generate all my code and queries for me

I will for future reference adopt your wise ways oh master.

And as for the code, I now get;

Error in GROUP BY clause.
Unable to parse query text.

If I remove the GROUP BY GROUPING SET line, the query will run but then fail saying it's can't run as it's not grouped. but the error above goes away...

Mucho Confusedo

 

[Arg81]

Unfortunately this has completely stumped me, and I can't get it into my head properly. I need to work out how to understand it because there are a lot of queries I want to be doing that would work this way.

On top of the above, I also need to add a count of the products send for that customer (so the relationship there is tbl_Requests 1 ---- many tbl_Samples) so that a user can see "Ah, 40 requests and we sent 152 items"....

Going to see if I can google this "GROUPING SET", at the moment I'm not sure what's wrong with the SQL I used from what you provided me..

ho-hum :/

 

[cjard]

Er.. Note of course that GROUPING SETS are an Oracle facility and might not be present in sql server (it's not my fault that youre using a rubbish database! )

the SQL server equivalent might be:

SELECT
  CustomerSiteName, 
  Sum(TotalDWR)
[B]FROM[/B]
(
  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, 
    reqs_agg.TotalSampleRequest AS TotalRequest
  FROM 
    CustomerSite cs
 
    LEFT OUTER JOIN
    DWR dw 
    ON 
      cs.CustomerSiteID = dw.CustomerSiteID 
 
    LEFT OUTER JOIN
    (
      SELECT     
        CustomerSiteID, 
        COUNT(RequestNumber) AS TotalSampleRequest
      FROM
        SampleRequest
      GROUP BY 
        CustomerSiteID
    ) reqs_agg 
    ON 
      reqs_agg.CustomerSiteID = cs.CustomerSiteID
 
  WHERE
    cs.CustomerID = 115
  GROUP BY 
    cs.CustomerSiteName, 
    reqs_agg.TotalSampleRequest
)
GROUP BY CustomerSiteName WITH ROLLUP

rollup is a bit dumb, but it works here, because it progressively removes groupings

So:

GROUP BY a,b,c,d WITH ROLLUP

should present:

a,b,c,d1
a,b,c,d2
a,b,c,d3
a,b,c,null
a,b,null,null
a,null,null,null
null,null,null

hopefully...

 

[cjard]

http://msdn2.microsoft.com/en-us/library/ms189305.aspx

 

[Arg81]

SELECT     CustomerSiteName, SUM(TotalDWR) AS Expr1, SUM(ActiveDWR) AS Expr2, SUM(CompleteDWR) AS Expr3, SUM(TotalRequest) AS Expr4
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, reqs_agg.TotalSampleRequest AS TotalRequest
                       FROM          CustomerSite AS cs LEFT OUTER JOIN
                                              DWR AS dw ON cs.CustomerSiteID = dw.CustomerSiteID LEFT OUTER JOIN
                                                  (SELECT     CustomerSiteID, COUNT(RequestNumber) AS TotalSampleRequest
                                                    FROM          SampleRequest
                                                    GROUP BY CustomerSiteID) AS reqs_agg ON reqs_agg.CustomerSiteID = cs.CustomerSiteID
                       WHERE      (cs.CustomerID = @CustomerID)
                       GROUP BY cs.CustomerSiteName, reqs_agg.TotalSampleRequest) AS derivedtbl_1
GROUP BY CustomerSiteName WITH ROLLUP

That works (kind of). I get all the results in a grid, and then get a row with "NULL" as the CustomerSiteName, with the values in the columns for that row the totals of all other rows (see screenshot)


Would it be easier for me to create a number of queries, and then relate them together to display the data across the grid and labels?

>> Else how do I get the labels to display only the "null" row values?? and then in theory I don't want the grid to show it.


Thanks for the help so far, I kinda get it now searching for GROUPING SETS started pointing to Oracle and I thought "hmmmmmm"

 

[cjard]

>> Else how do I get the labels to display only the "null" row values?? and then in theory I don't want the grid to show it.

Er.. you can yank it out of the datatable, and into another that is bound to the labels?

Dim rows as MyDataRow() = myDataTable.Select("CustomerSiteName IS NULL")
myDataTableLabelsAreBoundTo.Clear()
myDataTableLabelsAreBoundTo.addRow(rows(0))
myDataTable.Rows.Remove(rows(0))


or something like that!

 

[cjard]

Or you can have the labels bound through another bindingsource and set that labelsBindSource.Filter = "CustomerSiteName IS NULL"

Thats mucho easier..

grid-->gridBS.Filter NOT NULL-->datatable
label-->labelBS.Filer NULL-------^