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]

Right, changes made but not working.

IF a Customer has at least 1 DWR AND 1 Sample Request it works. IF a Customer has one but not the other, no data is returned.

That's why I thought a merge might be better...dunno though, never used one

 

[Arg81]

Got a merge to work...although now hit another problem :/

Merge Code;

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] frmCustomerDetails_Load([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].Load[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].lblCustomer.Text = varCustomerName[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].lblCustomerNameInfo.Text = varCustomerName[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].CustomerSiteDWRCountTableAdapter.FillByCustomerID([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerSiteDWRCount, varCustomerID)[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].CustomerSiteRequestCountTableAdapter.FillByCustomerID([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerSiteRequestCount, varCustomerID)[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Exception[/SIZE]
[SIZE=2]MessageBox.Show(ex.Message)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerSiteDWRCount.Merge([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerSiteRequestCount)[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].grdSitesInfo.DataSource = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerSiteDWRCount[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]

So it merges as CustomerSiteName exists in both DataTables.
Does what I want it to do, BUT...

The reason is, I then use the following code;

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2][COLOR=#000000] TotalDWRInfo()[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rows [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].grdSitesInfo.Splits(0).Rows.Count[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] value [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] i [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] i = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] rows - 1[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] s [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Int32 = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].grdSitesInfo(i, [/SIZE][SIZE=2][COLOR=#a31515]"TotalDWR"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]value = value + s[/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE][SIZE=2] i[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].lblTotalDWRInfo.Text = value[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[/COLOR][/SIZE]

to then display the totals - there are in fact three of these subs, TotalDWRInfo() , ActiveDWRInfo() and CompleteDWRInfo() , each calculate the total of the grid column and display in the relative labels.
Of course, these don't work when the field is null, as it's looking for an integer.....

 

[cjard]

IF a Customer has at least 1 DWR AND 1 Sample Request it works. IF a Customer has one but not the other, no data is returned.

Uhuh.. that's what an INNER JOIN does!

make it a left outer join ..

 

[Arg81]

Yeah, I realised...completely embarrassed now

 

[cjard]

SELECT     CustomerSite.CustomerID, 
               COUNT(DWR.DWRNumber) AS TotalDWRs, 
               SUM(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END) AS ActiveDWRs, 
               SUM(CASE WHEN StatusID = 0 THEN 1 ELSE 0 END) AS CompleteDWRs
[B]             ISNULL(SUM(reqs_agg.CountOfReqs), 0) as countofreqs[/B]
 
FROM         CustomerSite INNER JOIN
                      DWR ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID
 
[B]LEFT OUTER JOIN[/B]
[B](SELECT siteID, count(*) as COuntOfReqs FROM samplerequests GROUP BY siteID) reqs_agg ON customersite.siteid = reqs_agg.siteID[/B]
 
WHERE     (CustomerSite.CustomerID = @CustomerID)
 
GROUP BY CustomerSite.CustomerID

 

[Arg81]

Nope, that only shows 0 in CountOfReqs if the Site already has a DWR.

Test Data is:
1 customer, say CustomerA.

CustomerA has 4 sites, CustSiteA, CustSiteB, CustSiteC and CustSiteD.

Sites A&B Have 1 DWR each and 0 Requests each.
Site C&D have 0 DWRs each and 2 Requests each.

The results I get using a Left Outer Join like above are;

Site A 1 0
Site B 1 0

Site C and D don't show, and should be 0 2 in the results above....

 

[cjard]

Site C&D have 0 DWRs each

Youre gonna kick yourself:


Because the DWRS table is inner joined too..

 

[cjard]

SELECT     CustomerSite.CustomerID, 
               COUNT(DWR.DWRNumber) AS TotalDWRs, 
               [B]ISNULL[/B](SUM(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END),[B]0)[/B] AS ActiveDWRs, 
               [B]ISNULL[/B](SUM(CASE WHEN StatusID = 0 THEN 1 ELSE 0 END),[B]0)[/B] AS CompleteDWRs
[B]            ISNULL(SUM(reqs_agg.CountOfReqs), 0) as countofreqs[/B]
 
FROM         
  CustomerSite
[B]  LEFT OUTER JOIN[/B]
[B]  DWR [/B]
[B]    ON CustomerSite.CustomerSiteID = DWR.CustomerSiteID[/B]
 
[B]  LEFT OUTER JOIN[/B]
[B]  (SELECT siteID, count(*) as COuntOfReqs FROM samplerequests GROUP BY siteID) reqs_agg [/B]
[B]    ON customersite.siteid = reqs_agg.siteID[/B]
 
WHERE     (CustomerSite.CustomerID = @CustomerID)
 
GROUP BY CustomerSite.CustomerID

 

[Arg81]

Need...more...beer

The following 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,
ISNULL(SUM(reqs_agg.TotalSampleRequest), 0) 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

as my Query, will bring back 2080 Requests for a site, when in fact there are only 66....

If I do a simple Select COUNT(RequestNumber) FROM ..... then I can see that there are only 66 requests, not sure how or why it's getting 2080.

It's wrong for every customer I try, with the value changing, and I can't see any darn link between the true value and the one it's coming back with...

PS, the DWR values are coming back OK, it's only the Sample Request values that are wrong.

 

[cjard]

any way you can give me access to your database to run queries against it?

run this and tell me what you get:

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

 

[cjard]

actually, i'm expecting a block of results, 32 rows, and a column of totalrequests of 65


I think your 66 is a typo, because 2080 / 65 = 32

32 rows for this customer, from customer site, combined with 65 sample requests:

dwr1, 65
dwr2, 65
dwr3, 65
...
dwr32, 65


then sum the column of 65 sample requests = 2080


you need to be very mindful of how your database works.

JOINs are always done before aggregates.
We know that, for a given customer ID, the following query:

(SELECT CustomerSiteID, COUNT(RequestNumber) AS TotalSampleRequest FROM SampleRequest
GROUP BY CustomerSiteID) AS reqs_agg

can only return ONE row per customer id


but I also know from what you said about your DB before, a CustomerSite DOESNT have just Customer ID as pk. So you might have:

CustID, SiteID
1, 1
1, 2
1, 3
1, 4
1, 5
...
1, 32


if you tag on your already summarised data:

CustID, SiteID, TotalRequests
1, 1, 65
1, 2, 65
1, 3, 65
...
1, 32, 65


then sum all the 65s you get to 2080


dont do this! Eithe group by the 65 cause its the same all the way down, or take the MAX() not the SUM() - they are all the same,so MAX() will get your calc right. It wont always be so!

 

[Arg81]

You're spot on there. Just ran a few tests and replacing the code with what you've provided above works a treat.

Unforunately I gotta work on this most of today....still got a few niggles of the data import to sort out, wasn't expecting them so I've learnt from my mistakes already - never do an upgrade again!
It's a long story of why the data migration is being an ass...

edit
OK so the SQL works perfectly, but on my form I use the following code to "total" the column up.

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] TotalRequestInfo()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rows [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].grdSitesInfo.Splits(0).Rows.Count
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] value [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] i [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] i = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] rows - 1
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] s [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Int32 = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].grdSitesInfo(i, [/SIZE][SIZE=2][COLOR=#a31515]"TotalRequest"[/COLOR][/SIZE][SIZE=2])
value = value + s
[/SIZE][SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE][SIZE=2] i
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].lblTotalRequestInfo.Text = value
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

If a row is null, the above code throws an error "can't convert from DBNull to Integer". I can't work out how I can change the code so that it will display a 0 if the row is null...(either in the SQL - which I doubt, or in the code above). I've tried fiddling with the grid properties to display a 0 if null, but that's not working....maybe I'm doing it wrong!

 

[cjard]

SQLServer is something like:

SELECT ISNULL(col_might_be_null, 0) FROM table


In VB code, to exclude:

For Each ro in MyDatatable
  If Not ro.IsCOL_MIGHT_BE_NULLNull() Then total += ro.COL_MIGHT_BE_NULL

But why get vb to add things up? thats like opening word and getting a VB macro to add a list of numbers! Use Excel (the database) - it adds things up for fun!


Most proper databases will do CUBE, ROLLUP or GROUPING SETS

Heres a simple example:

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

the reults will look like:

USA, florida, store54, 1000
USA, florida, store55, 4000
USA, null, null, 5000
UK, derbyshire, store97, 2000
UK, derbyshire, store98, 8000
UK, null, null 10000


Whenever a grouped column is null, it (can) means all the groups have been grouped

Of course, you should avoid grouping by columns that might be null, because while they dont get mixed in with the results, they created "duplicate lines"
suppose we have a line in the db for some reason the county and store are null, and this store has sold 1234.. the results will be:

UK, derbyshire, store97, 2000
UK, derbyshire, store98, 8000
UK, null, null 10000
UK, null, null 1234


we solve this problem by:
a) having better data
b) there is a function of the DB that can tell you whether a row contains a rolled up total or not.. in oracle this is GROUPING() but we will leave that for later - recommend clean data sets instead...



if youre struggling to get to grips with it, consider this:

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

Same ends, but less efficient because it requires two table scans. You can think of gropuping sets as running these two queries in parallel with one table scan

 

[Arg81]

Some sites may have a null value, but I need the total for "customer" level so it sums the column up.

I assumed it would be easier to get the program to add the values than trying to execute another SQL query...

 

[cjard]

I assumed it would be easier to get the program to add the values than trying to execute another SQL query...

Rarely.. you just wrap the sql in another one:

SELECT whatever, sum(whatever)
FROM
(
  ..existing sql..
)
GROUP BY GROUPING SETS ((whatever),(less specific whatever))