Selecting Count

[mattmontalto]

I am trying to select the count of multiple columns from 2 different tables that are relational.... Best way for me to give you the whole picture is to show you the script I am using to select the data that I need a count of.... however this script selects the actual data from the columns I need from both tables.... I am trying to select this exact query except that instead of getting the values for the fields, I just want to get a count of them.... - The are all bit fields that I am trying to get a count of.... essentially a count of the number of values that are "1"

Can someone help me solve this... thank you

Here is the script:

SELECT
dbo.INTAKE_Tbl.fileno,
dbo.INTAKE_Tbl.[date] AS [Intake Date],
dbo.CVB_Tbl.[date] AS [Service Date],
dbo.INTAKE_Tbl.firstname,
dbo.INTAKE_Tbl.lastname,
dbo.INTAKE_Tbl.dob,
dbo.CVB_Tbl.newintakecase,
dbo.INTAKE_Tbl.race_amerindian,
dbo.INTAKE_Tbl.race_asian,
dbo.INTAKE_Tbl.race_blkaframer,
dbo.INTAKE_Tbl.race_hisplatino,
dbo.INTAKE_Tbl.race_ntchawaiinpacislnd,
dbo.INTAKE_Tbl.race_whtnonlatinocauc,
dbo.INTAKE_Tbl.race_someotherrace,
dbo.INTAKE_Tbl.race_multi,
dbo.INTAKE_Tbl.race_notdisclosed,
dbo.INTAKE_Tbl.gndr_male,
dbo.INTAKE_Tbl.gndr_female,
dbo.INTAKE_Tbl.gndr_other_expl,
dbo.INTAKE_Tbl.gndr_notdisclosed,
dbo.INTAKE_Tbl.agp_0_12,
dbo.INTAKE_Tbl.agp_13_17,
dbo.INTAKE_Tbl.agp_18_24,
dbo.INTAKE_Tbl.agp_25_59,
dbo.INTAKE_Tbl.agp_60_oldr,
dbo.INTAKE_Tbl.agp_notdiclosed,
dbo.INTAKE_Tbl.assault,
dbo.INTAKE_Tbl.adultsexualabuse,
dbo.INTAKE_Tbl.vt_adltsexabuseaschild,
dbo.INTAKE_Tbl.vt_arson,
dbo.INTAKE_Tbl.vt_bullying_cyverphy,
dbo.INTAKE_Tbl.burgulary,
dbo.INTAKE_Tbl.childphysicalabue,
dbo.INTAKE_Tbl.vt_childporn,
dbo.INTAKE_Tbl.childsexualabuse,
dbo.INTAKE_Tbl.domesticviolence,
dbo.INTAKE_Tbl.dwidui,
dbo.INTAKE_Tbl.elderabuse,
dbo.INTAKE_Tbl.vt_hatecrime,
dbo.INTAKE_Tbl.vt_humtrfk_lbr,
dbo.INTAKE_Tbl.vt_humtrfk_sex,
dbo.INTAKE_Tbl.vt_idtheft_fraud,
dbo.INTAKE_Tbl.vt_kidnap_noncust,
dbo.INTAKE_Tbl.vt_kidnap_cust,
dbo.INTAKE_Tbl.vt_massviolence,
dbo.INTAKE_Tbl.vt_vehicular_h_n_r,
dbo.INTAKE_Tbl.robbery,
dbo.INTAKE_Tbl.vt_stalk_harrass,
dbo.INTAKE_Tbl.familyofhomicide,
dbo.INTAKE_Tbl.vt_teendatevic,
dbo.INTAKE_Tbl.vt_terrorism,
dbo.INTAKE_Tbl.vt_viol_ct_ordr,
dbo.INTAKE_Tbl.vt_other,
dbo.INTAKE_Tbl.sclass_deaf,
dbo.INTAKE_Tbl.sclass_homeless,
dbo.INTAKE_Tbl.sclass_immigrant_rfg_asy,
dbo.INTAKE_Tbl.sclass_lgntq,
dbo.INTAKE_Tbl.sclass_veteran,
dbo.INTAKE_Tbl.sclass_victim_cog_phys_mntl_disable,
dbo.INTAKE_Tbl.sclass_victim_limtd_eng,
dbo.INTAKE_Tbl.sclass_other,
dbo.CVB_Tbl.cvbinfoandassess,
dbo.CVB_Tbl.inforeferral,
dbo.CVB_Tbl.a1_info_cjp,
dbo.CVB_Tbl.a2_vicrts,
dbo.CVB_Tbl.a3_referothpgrm,
dbo.CVB_Tbl.a4_referothsvcs,
dbo.CVB_Tbl.personaladvocacy,
dbo.CVB_Tbl.b1_acc_emer_medcare,
dbo.CVB_Tbl.b2_acc_medforexam,
dbo.CVB_Tbl.b3_acc_int_lawadv,
dbo.CVB_Tbl.b4_indabvoc,
dbo.CVB_Tbl.b5_perf_medexam,
dbo.CVB_Tbl.b6_immagr_asst,
dbo.CVB_Tbl.b7_interv_empl_ll_sch,
dbo.CVB_Tbl.b8_cld_dep_careasst,
dbo.CVB_Tbl.b9_agncy_trnspt,
dbo.CVB_Tbl.b10_interpret_svcs,
dbo.CVB_Tbl.counseling,
dbo.CVB_Tbl.c1_crisis_interv,
dbo.CVB_Tbl.c2_hotline_crisis,
dbo.CVB_Tbl.c3_onscene_crisis_resp,
dbo.CVB_Tbl.c4_indiv_council,
dbo.CVB_Tbl.c5_supp_grps,
dbo.CVB_Tbl.c6_therapy,
dbo.CVB_Tbl.c7_emer_finan_asst,
dbo.CVB_Tbl.emergencyassist,
dbo.CVB_Tbl.d_shelter_house_svcs,
dbo.CVB_Tbl.d1_emer_safehse,
dbo.CVB_Tbl.d2_tradtl_house,
dbo.CVB_Tbl.d3_reloacte_asst,
dbo.CVB_Tbl.crimejustsupport,
dbo.CVB_Tbl.e1_notif_of_crim,
dbo.CVB_Tbl.e2_vic_impt_stmt,
dbo.CVB_Tbl.e3_restitut_asst,
dbo.CVB_Tbl.e4_atty_prot_restr_ordr,
dbo.CVB_Tbl.e5_atty_asst_famlaw,
dbo.CVB_Tbl.e6_oth_emer_justice_rel,
dbo.CVB_Tbl.e7_atty_immigr_asst,
dbo.CVB_Tbl.e8_prosec_intv_accomp,
dbo.CVB_Tbl.e9_law_enf_int_accomp,
dbo.CVB_Tbl.e10_crim_advoc_accomp,
dbo.CVB_Tbl.e11_oth_leg_consul
FROM dbo.INTAKE_Tbl
LEFT OUTER JOIN dbo.CVB_Tbl ON dbo.INTAKE_Tbl.fileno = dbo.CVB_Tbl.intake_fileno

where dbo.INTAKE_Tbl.date between '2016-05-1 00:00:00:000' and '2016-05-31 00:00:00:000'
and dbo.CVB_Tbl.date between '2016-05-1 00:00:00:000' and '2016-05-31 00:00:00:000'

ORDER BY dbo.INTAKE_Tbl.fileno DESC

 

[TamarGranor]

Are you trying to find out how many records have a given field set to true, or how many of the fields in a particular record are set to true? That is, do you want to know how many people in your data set have a particular characteristic, or how many characteristics a particular person in your data set has?

Tamar

 

[mattmontalto]

Tamar,

Thank you for your response, I need the total count for each field. I need to be able to tell how many times these categories were selected in a given month (hense the "where" statement at the end. Thanks.

Matt

 

[Olaf Doschke]

This is mainly a tefdious work, for example you need [tt]SELECT race_amerindian, Count(*) From dbo.INTAKE_Tbl GROUP BY race_amerindian[/tt] As you're only interested in the value 1 of bit fields, you add [tt]WHERE race_amerindian=1[/tt] in the general case you'd get all distinct values with their count.

Aside of that your month selection is wrong, you miss the whole last day of may, if any datetime values of 31st of may have a time porion after midnight. The simples where clause would be YEAR(INTAKE_Tbl.date)=2016 and MONTH(INTAKE_Tbl.date)=5

And besides these two things, you can't join without influencing the counts, that means if there are several records in CVB_Tbl for every row in INTAKE_Tbl the number of matches is a factor to the count contributed by that single record, so you'd query the counts independant on each other.

Overall this sounds more like you need some kind of pivoting. More info on the intention would be needed to create a pivoting query.

Bye, Olaf.

 

[mattmontalto]

Olaf,

I know about the date time range matter.... fact is that the way the data is being recorded into the database, the time is always simply following the date as 00:00:00.000 --- This is fine and not something that I am concerned with. I just simply need to be able to select each of these fields from the database tables and have a number of times each field was selected in a given months time...

I intend on running this query to pull the date into excel so then end user can see one line of data giving them the total monthly for all the columns.

unfortunately I do not know how to compose this all into one query though... which is what brings me here..... thank you for any help you can provide

Matt M

 

[SgtJarrow]

Olaf has provided some good insight as to this not always workig right due to the nature of the joins. In order to garuantee completely accurate results, you should sum both tables separately and then join them together and perform a final sum.

But, here is a sample that might help you find a path that will work with your current setup. Based on the sample data I feed it, the results come out correct. But please check your work before accepting this as the answer. Depending on your data, it might not be truly accurate.

Another note: Without seeing a sample of your data, you may be able to remove the Date Ranges for CVB_Table in your WHERE clause. Unless the records in the CVB_Table are inserted into the table on a different day than the INTAKE_Table, the JOIN by FileNo should be sufficient to create hooks. Then you only need to filter by the INTAKE_Table. If the tables can have records added/modified on separate dates and you are concerned with this, then your query is right. But as a thought-provoking question: If the records in INTAKE_Table does not meet the Date Range, no matching record in the CVB_Table will be picked up anyway. And if the record in the INTAKE_Table is in scope of the Date Range and the record(s) in the CVB_Table do not fall in the Date Range provided, nothing from the CVB_Table for the matching File Number will be counted anyway. Seems awefully like the second Date Range in the filter is redundant in this instance and will do nothing but affect the performance of the query as it evaluates each record for matching criteria. It'd be something I would kick back from any developer on my team for tighter review before that could go into a production environment.

Basically, the first four parts of the script are just creating two tables and some dummy data to work with. There is an example of your query and how the test data looks compared to what your data should look like. The final query in the script is what makes the magic happen.... Check each column to see if the bit is set to 1. if so, provide an integer of 1 for that record result and an integer of 0 when not. Sum all the 1s found in each column and you have your results.

Note that in the final query, you HAVE to remove the File Number column, the Date column and any other column you are not count/summing. This is because you have asked for an overall count. If you want count by day or month or whatever, you would add in a GROUP BY clause that can provide that level of summing.

Any questions, please bring them here for additonal assistance. Hope it helps you find a final solution to your problem...

DECLARE @Table1 TABLE
(
	FileNumber		INT PRIMARY KEY,
	FileDateTime	        DATE,
	Value1			BIT,
	Value2			BIT,
	Value3			BIT
);

DECLARE @Table2 TABLE
(
	FileNumber		INT PRIMARY KEY,
	FileDateTime	        DATE,
	Value4			BIT,
	Value5			BIT,
	Value6			BIT
);

INSERT INTO @Table1 (FileNumber, FileDateTime, Value1, Value2, Value3) VALUES (1, '2016-06-15', 1, 0, 1);
INSERT INTO @Table1 (FileNumber, FileDateTime, Value1, Value2, Value3) VALUES (2, '2016-06-15', 1, 1, 1);
INSERT INTO @Table1 (FileNumber, FileDateTime, Value1, Value2, Value3) VALUES (3, '2016-06-14', 0, 1, 1);
INSERT INTO @Table1 (FileNumber, FileDateTime, Value1, Value2, Value3) VALUES (4, '2016-06-14', 0, 0, 1);
INSERT INTO @Table1 (FileNumber, FileDateTime, Value1, Value2, Value3) VALUES (5, '2016-06-13', 1, 1, 1);
INSERT INTO @Table1 (FileNumber, FileDateTime, Value1, Value2, Value3) VALUES (6, '2016-06-13', 1, 0, 1);

INSERT INTO @Table2 (FileNumber, FileDateTime, Value4, Value5, Value6) VALUES (1, '2016-06-15', 1, 1, 1);
INSERT INTO @Table2 (FileNumber, FileDateTime, Value4, Value5, Value6) VALUES (2, '2016-06-15', 1, 1, 1);
INSERT INTO @Table2 (FileNumber, FileDateTime, Value4, Value5, Value6) VALUES (3, '2016-06-14', 0, 0, 1);
INSERT INTO @Table2 (FileNumber, FileDateTime, Value4, Value5, Value6) VALUES (4, '2016-06-14', 1, 0, 0);
INSERT INTO @Table2 (FileNumber, FileDateTime, Value4, Value5, Value6) VALUES (5, '2016-06-13', 1, 1, 0);

--Your Query
SELECT
	t1.FileNumber,
        t1.FileDateTime,
        t1.Value1,
        t1.Value2,
        t1.Value3,
	t2.Value4,
	t2.Value5,
	t2.Value6
FROM @Table1 t1
LEFT JOIN @Table2 t2
	ON t1.FileNumber = t2.FileNumber
WHERE t1.FileDateTime BETWEEN '2016-06-01' AND '2016-06-30'
	AND t2.FileDateTime BETWEEN '2016-06-01' AND '2016-06-30';

--Sample Query.  Note the FileNumber and FileDateTime have to be removed for the SUM to work over the entire data range.
SELECT
	--t1.FileNumber,
        --t1.FileDateTime,
        SUM(CASE WHEN t1.Value1 = 1 THEN 1 ELSE 0 END) Value1Count,
        SUM(CASE WHEN t1.Value2 = 1 THEN 1 ELSE 0 END) Value2Count,
        SUM(CASE WHEN t1.Value3 = 1 THEN 1 ELSE 0 END) Value3Count,
	SUM(CASE WHEN t2.Value4 = 1 THEN 1 ELSE 0 END) Value4Count,
	SUM(CASE WHEN t2.Value5 = 1 THEN 1 ELSE 0 END) Value5Count,
	SUM(CASE WHEN t2.Value6 = 1 THEN 1 ELSE 0 END) Value6Count
FROM @Table1 t1
LEFT JOIN @Table2 t2
	ON t1.FileNumber = t2.FileNumber
WHERE t1.FileDateTime BETWEEN '2016-06-01' AND '2016-06-30'
	AND t2.FileDateTime BETWEEN '2016-06-01' AND '2016-06-30';

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[Olaf Doschke]

Well, you can't. You can't have all the counts in one query, as the condition would need to be all bit fields=1, while they are not all 1 for most records.
So you have to pull each count in a separate query or have something like SUM(CAST(field as int)) for each field to count the 1 bits.

Bye, Olaf.

Edit: this overlapped with the other answer, we'll see if that helps.

 

[mattmontalto]

Thank you Sgt Jarrow and olaf... (and TamarGranor) for all your help.

Sgt.... thanks for giving me this example etc.... I appreciate your work... however it is a bit complicated for me to put into action.....

I am able to get totals for all these columns by using the php web application that the data gets entered through.. but it looks terrible as there are so many fields to view....

I am able to export the data to excel directly from the application. Problem is though that it is also awkward to look at there as well when exported.... what I would like to do is take the data that is exported and populate another excel sheet (that would be a template) with the values from the one row at the end that contains the totals.... obviously the template would be the names of the fields I am counting....

Is this something that I should address in a different forum?..... I would think so, but just throwing it our here in case anyone has any experience with that.... thank you

Matt

 

[SgtJarrow]

That sounds like something better asked in another forum. Perhaps Microsoft Office (

http://www.tek-tips.com/threadminder.cfm?pid=68)

or Microsoft Office 365 (

http://www.tek-tips.com/threadminder.cfm?pid=1853).

That type of Excel comparison/join/manipulation is not covered in this forum nor my area of expertise to continue assisting down that aveune. Good luck.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[mattmontalto]

Thank you so much for all your help.... I just posted it there in the MS Office Forum. Have a great day... and Robert (SgtJarrow)... Thank you for your service

Matt M

 

[SgtJarrow]

It was my honor and my pleasure. Thanks for the shout out.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer