Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

SQL query to counts records by field.

I have the following SQL query that counts the data but it counts based on the Respondent_name and I need to to count based on the Respondent_name and report_prd.

SELECT a.[respondent_name],report_prd, COUNT(report_prd) AS Cnt --b.respondent_id
FROM [eqr_ident_attsttn$] a INNER JOIN
    (SELECT respondent_id FROM [trans_201212_al] UNION ALL
    SELECT respondent_id FROM [trans_201212_mz] UNION ALL
    SELECT respondent_id FROM [trans_201209_al] UNION ALL
    SELECT respondent_id FROM [trans_201209_mz] UNION ALL
    SELECT respondent_id FROM [trans_201209_388] UNION ALL
    SELECT respondent_id FROM [trans_201206_al] UNION ALL
    SELECT respondent_id FROM [trans_201206_mz] UNION ALL
    SELECT respondent_id FROM [trans_201206_388] UNION ALL
    SELECT respondent_id FROM [trans_201203_al] UNION ALL
    SELECT respondent_id FROM [trans_201203_mz]) b ON a.respondent_id = b.respondent_id
    where report_yr like '2012' --and report_prd like 12
GROUP BY report_prd, a.[respondent_name]

I need to know the count per respondent_name per report_prd. Any ideas?
0
jimmylew52
Asked:
jimmylew52
  • 18
  • 12
  • 8
  • +2
2 Solutions
 
dsackerContract ERP Admin/ConsultantCommented:
Have you reversed your GROUP BY, so that it reads:

GROUP BY a.[respondent_name],report_prd

??
0
 
jimmylew52Author Commented:
Tried that but I still get a total for both report_prd as a total number and not for each report_prd.

And it groups by report_prd first instead of respondent_name
0
 
dsackerContract ERP Admin/ConsultantCommented:
Change COUNT(report_prd) to COUNT(1)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
jimmylew52Author Commented:
I need the following format:

Respondent_name          Report_prd          #
cust1                                3                          6
cust1                                6                          4
cust1                               12                         2

What I get is:

Respondent_name          Report_prd          #
cust1                                3                          12
cust1                                6                          12
cust1                               12                         12
0
 
dsackerContract ERP Admin/ConsultantCommented:
Is 3, 6, 12 the value for Report_prd, or is that also a count you desire?
0
 
Scott PletcherSenior DBACommented:
SELECT a.[respondent_name], b.report_prd, SUM(b.report_prd_count) AS Cnt --b.respondent_id
FROM [eqr_ident_attsttn$] a
INNER JOIN
    (SELECT respondent_id, report_prd, COUNT(report_prd) AS report_prd_count FROM [trans_201212_al] WHERE report_yr like '2012' GROUP BY respondent_id, report_prd UNION ALL
    SELECT respondent_id, report_prd, COUNT(report_prd) AS report_prd_count FROM [trans_201212_mz] WHERE report_yr like '2012' GROUP BY respondent_id, report_prd UNION ALL
    SELECT respondent_id, report_prd, COUNT(report_prd) AS report_prd_count FROM [trans_201209_al] WHERE report_yr like '2012' GROUP BY respondent_id, report_prd UNION ALL
    SELECT respondent_id, report_prd, COUNT(report_prd) AS report_prd_count FROM [trans_201209_mz] WHERE report_yr like '2012' GROUP BY respondent_id, report_prd UNION ALL
    SELECT respondent_id, report_prd, COUNT(report_prd) AS report_prd_count FROM [trans_201209_388] WHERE report_yr like '2012' GROUP BY respondent_id, report_prd UNION ALL
    SELECT respondent_id, report_prd, COUNT(report_prd) AS report_prd_count FROM [trans_201206_al] WHERE report_yr like '2012' GROUP BY respondent_id, report_prd UNION ALL
    SELECT respondent_id, report_prd, COUNT(report_prd) AS report_prd_count FROM [trans_201206_mz] WHERE report_yr like '2012' GROUP BY respondent_id, report_prd UNION ALL
    SELECT respondent_id, report_prd, COUNT(report_prd) AS report_prd_count FROM [trans_201206_388] WHERE report_yr like '2012' GROUP BY respondent_id, report_prd UNION ALL
    SELECT respondent_id, report_prd, COUNT(report_prd) AS report_prd_count FROM [trans_201203_al] WHERE report_yr like '2012' GROUP BY respondent_id, report_prd UNION ALL
    SELECT respondent_id, report_prd, COUNT(report_prd) AS report_prd_count FROM [trans_201203_mz] WHERE report_yr like '2012' GROUP BY respondent_id, report_prd
) b ON a.respondent_id = b.respondent_id
--WHERE b.report_yr like '2012' --and report_prd like 12
GROUP BY b.report_prd, a.[respondent_name]
0
 
DultonCommented:
Try replacing your "UNION ALL" statements with just "UNION" which will inherently remove duplicates.

Possibly duplicate values (12 of each ) are coming out of the subquery?
0
 
jimmylew52Author Commented:
dsaker:

same problem usin(1) - 3, 6, 12 is the value for Report_prd

scottpletcher:  Many errors with your query - 3, 6, 12 the value for Report_prd

Dulton ;

Removing "all"  gives me a count of one for everything and I know that is not correct.
0
 
dsackerContract ERP Admin/ConsultantCommented:
Does this get you any closer?

SELECT respondent_name, report_prd, count(1) AS Cnt
FROM (
SELECT a.[respondent_name], report_prd
FROM [eqr_ident_attsttn$] a INNER JOIN
    (SELECT respondent_id FROM [trans_201212_al] UNION ALL
    SELECT respondent_id FROM [trans_201212_mz] UNION ALL
    SELECT respondent_id FROM [trans_201209_al] UNION ALL
    SELECT respondent_id FROM [trans_201209_mz] UNION ALL
    SELECT respondent_id FROM [trans_201209_388] UNION ALL
    SELECT respondent_id FROM [trans_201206_al] UNION ALL
    SELECT respondent_id FROM [trans_201206_mz] UNION ALL
    SELECT respondent_id FROM [trans_201206_388] UNION ALL
    SELECT respondent_id FROM [trans_201203_al] UNION ALL
    SELECT respondent_id FROM [trans_201203_mz]) b ON a.respondent_id = b.respondent_id
where report_yr like '2012' --and report_prd like 12
    ) t1
GROUP BY respondent_name, report_prd

Open in new window

0
 
Scott PletcherSenior DBACommented:
Make sure to refresh and get the last changes I made after seeing the comments you made right before I posted :-) .

Btw, it's more efficient to group each table separately rather than UNION and group them all together.
0
 
jimmylew52Author Commented:
dsacker:
Still getting the total for all 4 report_prd listed for each report)prd

Scottpletcher:
Refreshed and tried the code:  Many errors.
Invalid column name 'report_yr'.
Invalid column name 'report_prd'.
0
 
DultonCommented:
well, i'd be willing to bet 1 of your sources, either the table or the subquery has duplicate values that are multiplying your matches, causing you counts to be artificially inflated....

if you do indeed need the union all, then the only other thing would be to ensure you don't have duplicates coming from the other table.....

Given you say you need the duplicates from the union subquery, here I've ensured there aren't duplicates in the other table with a distinct subquery.


SELECT a.[respondent_name],a.report_prd, COUNT(a.report_prd) AS Cnt
FROM (select distinct [respondent_name], respondent_id, report_prd
        from [eqr_ident_attsttn$]
        where report_yr = '2012') as a INNER JOIN
    (SELECT respondent_id FROM [trans_201212_al] UNION ALL
    SELECT respondent_id FROM [trans_201212_mz] UNION ALL
    SELECT respondent_id FROM [trans_201209_al] UNION ALL
    SELECT respondent_id FROM [trans_201209_mz] UNION ALL
    SELECT respondent_id FROM [trans_201209_388] UNION ALL
    SELECT respondent_id FROM [trans_201206_al] UNION ALL
    SELECT respondent_id FROM [trans_201206_mz] UNION ALL
    SELECT respondent_id FROM [trans_201206_388] UNION ALL
    SELECT respondent_id FROM [trans_201203_al] UNION ALL
    SELECT respondent_id FROM [trans_201203_mz]) b
ON a.respondent_id = b.respondent_id
GROUP BY report_prd, a.[respondent_name]



If this isn't what you're after, i'd recommend you manually look at the contents of the subquery to ensure they're what you think they are. The count in the outer query will be the product of the two inner queries (or query and table)....so a couple duplicates will result in multiplied output.  Please do not take this request in any derogatory way, but the query is fairly basic, so there must be some unknown buried in the data that is causing these results from one (or both) of the sides of this join.
0
 
dsackerContract ERP Admin/ConsultantCommented:
You are probably joining all data to each report prod (Cartesian is happening somewhere), if that is happening.

Take a step back, perhaps: Take off the larger subselect, and run the query only from the eqr_ident_attsttn$ table. Do you get expected results only on the basis of that table's data?
0
 
jimmylew52Author Commented:
In the eqr_ident_attsttn$ table there is one name and ID number for each customer.

In the trans_XXXXXX_XX tables there is a respondent_id (and other information) for each transaction. There will be multiple entries for each respondent_id in this table.

I need to count the transactions for each customer per report_prd.

So far nothing looks like it works correctly.
0
 
dsackerContract ERP Admin/ConsultantCommented:
Is there a report_prd in the trans_XXXXXX_XX tables?

Pretty sure there is a Cartesian issue.
0
 
jimmylew52Author Commented:
Yes there is a  there a report_prd in the trans_XXXXXX_XX tables.

Sorry, don't know what a Cartesian issue is.
0
 
dsackerContract ERP Admin/ConsultantCommented:
A Cartesian query is where you did not join enough keys, so that it returns too many records per parent row.

Where you have:

ON a.respondent_id = b.respondent_id

... trying adding something like

ON a.respondent_id = b.respondent_id AND a.report_prod = b.report_prod

If that works, you found your hole.
0
 
jimmylew52Author Commented:
when I add   AND a.report_prd = b.report_prd

I get the following error:

Msg 207, Level 16, State 1, Line 12
Invalid column name 'report_prd'.
0
 
dsackerContract ERP Admin/ConsultantCommented:
I thought you said report_prd was in those trans_XXXXXX_XX tables?
0
 
jimmylew52Author Commented:
The data is there but it is called column 2, sorry about that, this is a converted db.
0
 
dsackerContract ERP Admin/ConsultantCommented:
So have you joined that to report_prd? :)
0
 
jimmylew52Author Commented:
results look the same. :-(
0
 
jimmylew52Author Commented:
Joined??? as in Union all???

or

AND 'Column 2' = 'Column 2'
0
 
dsackerContract ERP Admin/ConsultantCommented:
Probably something like:

ON a.respondent_id = b.respondent_id AND a.report_prod = b.[Column 2]
0
 
PortletPaulfreelancerCommented:
need revision - sorry
0
 
PortletPaulfreelancerCommented:
I started a query and gave up. Looks to me like there may be too many unknowns.

Respondent_name  Report_prd          #
cust1                                3                          6
cust1                                6                          4
cust1                               12                         2

What is the '#' column actually expected to count?

Does [report_yr] also exist in the trans_XXXXXX_XX tables?
It seems reasonable to assume this is true:
a. it's unlikely to be in an 'ident' table
b. is related to report_prd

>>Yes there is a report_prd in the trans_XXXXXX_XX tables.

This, with the assumption on [report_yr], then ScottPletcher's query structure above seems appropriate.  ( I wonder if you did run the correct version? Scott apparently did an update to it.)

All I'm doing here is adding a counting option to Scott's query for comparison purposes.
SELECT
       a.[respondent_name]
     , b.report_prd
     , COUNT(b.report_prd)          AS Cnt
     , SUM(b.report_prd)            AS Cnt_by_Sum
FROM [eqr_ident_attsttn$] a
INNER JOIN (
             -- nb: do NOT just use UNION in this query, UNION ALL is required.
             
             SELECT respondent_id, report_prd, count(*) as report_prd_count
             FROM [trans_201212_al]
             WHERE report_yr like '2012'        -- an asumption
             GROUP BY respondent_id, report_prd
             
             UNION ALL
             
             SELECT respondent_id, report_prd, count(*) as report_prd_count
             FROM [trans_201212_mz]
             WHERE report_yr like '2012'        -- an asumption
             GROUP BY respondent_id, report_prd
             
             UNION ALL
             
             SELECT respondent_id, report_prd, count(*) as report_prd_count
             FROM [trans_201209_al]
             WHERE report_yr like '2012'        -- an asumption
             GROUP BY respondent_id, report_prd
             
             UNION ALL
             
             SELECT respondent_id, report_prd, count(*) as report_prd_count
             FROM [trans_201209_mz]
             WHERE report_yr like '2012'        -- an asumption
             GROUP BY respondent_id, report_prd
             
             UNION ALL
             
             SELECT respondent_id, report_prd, count(*) as report_prd_count
             FROM [trans_201209_388]
             WHERE report_yr like '2012'        -- an asumption
             GROUP BY respondent_id, report_prd
             
             UNION ALL
             
             SELECT respondent_id, report_prd, count(*) as report_prd_count
             FROM [trans_201206_al]
             WHERE report_yr like '2012'        -- an asumption
             GROUP BY respondent_id, report_prd
             
             UNION ALL
             
             SELECT respondent_id, report_prd, count(*) as report_prd_count
             FROM [trans_201206_mz]
             WHERE report_yr like '2012'        -- an asumption
             GROUP BY respondent_id, report_prd
             
             UNION ALL
             
             SELECT respondent_id, report_prd, count(*) as report_prd_count
             FROM [trans_201206_388]
             WHERE report_yr like '2012'        -- an asumption
             GROUP BY respondent_id, report_prd
             
             UNION ALL
             
             SELECT respondent_id, report_prd, count(*) as report_prd_count
             FROM [trans_201203_al]
             WHERE report_yr like '2012'        -- an asumption
             GROUP BY respondent_id, report_prd
             
             UNION ALL
             
             SELECT respondent_id, report_prd, count(*) as report_prd_count
             FROM [trans_201203_mz]
             WHERE report_yr like '2012'        -- an asumption
             GROUP BY respondent_id, report_prd

             ) b ON a.respondent_id = b.respondent_id

--WHERE b.report_yr LIKE '2012' -- assuming this comes from alias a
--AND b.report_prd like 12

GROUP BY
       a.[respondent_name]
     , b.report_prd

Open in new window

{+ edit, sorry}
0
 
jimmylew52Author Commented:
dsacker:
ON a.respondent_id = b.respondent_id AND a.report_prd = b.[Column 2]
gives me an error Invalid column name 'Column 2'.
similar error no matter what column name I enter.

 PortletPaul:
What is the '#' column actually expected to count?  # of rows in Column 2

Does [report_yr] also exist in the trans_XXXXXX_XX tables?  Yes
It seems reasonable to assume this is true:
a. it's unlikely to be in an 'ident' table        no
b. is related to report_prd        Report_prd is a quarter in report_yr

I get the same errors:
Invalid column name 'report_yr'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'report_prd'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'report_prd'.
Msg 207, Level 16, State 1, Line 19
Invalid column name 'report_yr'.
Msg 207, Level 16, State 1, Line 20
etc
0
 
PortletPaulfreelancerCommented:
What is the '#' column actually expected to count?  # of rows in Column 2
>> not quite what I meant, there are nuances. e.g.

count(*)
count(distinct report_prd)
count(case when x=1 then report_prd else null)

each of these would give a count - but a different result.

The default is to count all records - is that what you are expecting?

OR: Could you describe what you want to count? (to explore the nuances)
0
 
dsackerContract ERP Admin/ConsultantCommented:
Hi Jimmy,

Can you post the layout of your trans_xxxxxx_xx table. One of those field names that holds the report_prd data, but is NOT named the same, is the field we need to join to a.report_prd. Once that is done, the query should start looking a lot better. Since we have already done much of the analysis you see repeated questions about, you should be able to stick with the simpler queries up top and avoid any complexities, once we find the proper field to join.
0
 
PortletPaulfreelancerCommented:
>>Does [report_yr] also exist in the trans_XXXXXX_XX tables? Yes

Invalid column name 'report_yr'.

no offence intended, but if that field does exist in those tables that error message above would not be produced.

Please provide the table definitions of all tables involved (as requested above).

It's a bit like that [column 2], we are thinking one thing, and you are thinking something else. It will help both you, and us, if we know precisely what the fields are please.
0
 
jimmylew52Author Commented:
I need to count the rows in the trans_XXXXXX_XX table that have column 1 (report_yr)  = 2012 and sort by name and column 2 (report_prd). should give a report similar to this:

Respondent_name          Report_prd                   # of rows
cust1                                3                                 6
cust1                                6                                 4
cust1                               12                                2

I don't NEED the headers, they are a convenience.


Table  eqr_ident_attsttn$ has columns   report_yr and report_prd in it as does tables trans_XXXXX_XX as column 1 & column 2.

I'm trying to reconvert the foxpro db into sql and maintain the field names this time. Hopefully that will eliminate some of the confusion. It is a time consuming process, will take the better part of the day.
0
 
jimmylew52Author Commented:
No offense taken. This has been an ongoing problem for me for over a month. I tried not to get others involved in the mess but I am beyond my understanding.

I found software recently that appears to convert the db from foxpro to sql and I am trying it to see the confusion is less.  It has been many years since I have used a foxpro db and I nor our dba could get anything useful out of it.

I tried posting a question for the foxpro people but I was not able to get what I needed, probably mostly because of my lack of knowledge of Foxpro.
0
 
dsackerContract ERP Admin/ConsultantCommented:
Hi Jimmy. Can you post the trans_xxxxxx_xx table layout (the column names, not the header names)?
0
 
PortletPaulfreelancerCommented:
as is "repondent_Id" also "column X"?
are there any more "column Y"s we should know about?
is [column 2] (report_prd) a string or a number?

how about this query, any better?
SELECT
       a.[respondent_name]
     , b.report_prd
     , COUNT(b.report_prd)          AS Cnt
     , SUM(b.report_prd_count)            AS Cnt_by_Sum
FROM [eqr_ident_attsttn$] a
INNER JOIN (
             -- nb: do NOT just use UNION in this query, UNION ALL is required.
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201212_al]
             WHERE [column 1] like '2012%'        -- an assumption about the data type (string)
             GROUP BY respondent_id, [column 2] as report_prd
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201212_mz]
             WHERE [column 1] like '2012%'        -- an assumption about the data type (string)
             GROUP BY respondent_id, [column 2] as report_prd
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201209_al]
             WHERE [column 1] like '2012%'        -- an assumption about the data type (string)
             GROUP BY respondent_id, [column 2] as report_prd
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201209_mz]
             WHERE [column 1] like '2012%'        -- an assumption about the data type (string)
             GROUP BY respondent_id, [column 2] as report_prd
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201209_388]
             WHERE [column 1] like '2012%'        -- an assumption about the data type (string)
             GROUP BY respondent_id, [column 2] as report_prd
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201206_al]
             WHERE [column 1] like '2012%'        -- an assumption about the data type (string)
             GROUP BY respondent_id, [column 2] as report_prd
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201206_mz]
             WHERE [column 1] like '2012%'        -- an assumption about the data type (string)
             GROUP BY respondent_id, [column 2] as report_prd
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201206_388]
             WHERE [column 1] like '2012%'        -- an assumption about the data type (string)
             GROUP BY respondent_id, [column 2] as report_prd
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201203_al]
             WHERE [column 1] like '2012%'        -- an assumption about the data type (string)
             GROUP BY respondent_id, [column 2] as report_prd
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201203_mz]
             WHERE [column 1] like '2012%'        -- an assumption about the data type (string)
             GROUP BY respondent_id, [column 2] as report_prd

             ) b ON a.respondent_id = b.respondent_id

--WHERE b.report_yr LIKE '2012' -- assuming this comes from alias a
--AND b.report_prd like 12

GROUP BY
       a.[respondent_name]
     , b.report_prd

Open in new window

0
 
jimmylew52Author Commented:
received this error this time:

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)


as is "repondent_Id" also "column X"? >>  respondent_id is the same name in both tables
are there any more "column Y"s we should know about?  >>  not that I think a relavent
is [column 2] (report_prd) a string or a number?     >>  varchar
0
 
PortletPaulfreelancerCommented:
If [respondent_id] is actually that name
, and [column 2] holds "report_prd"
, and [column 1] holds "report_yr"
, and [column 1] is varchar
then the following should work without error:

            SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201212_al]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]

IF this snippet does produce an error it is most probably "respondent_id" and that reference should be changed to the actual field name (column heading).

If that snippet does work without error, but supplies no data, then the where clause is incorrect, an alternative might be:

             WHERE [column 1] like '%2012%'

It would be preferable to use equals instead of like, so this could be tried also:

             WHERE [column 1] = '2012'

Once this snippet is working that logic can be expended to each of the 9 remaining tables of the union all part of the query..
-- start small
-- get just this bit working first
-- verify the field names [respondent_id], [column 1], [column 2]
-- check the where clause works with your data

             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201212_al]
             WHERE [column 1] = '2012'        -- first preference, choose only one of these 3
             --WHERE [column 1] like '2012%'      -- should work
             --WHERE [column 1] like '%2012%'        -- might be needed
             GROUP BY respondent_id, [column 2]

-- when this bit is working, apply the same logic to each of the similar tables
-- perhaps test each one individually in case there are differences             
;


-- don't use the following until the above is proven
-- apply the workable logic as required by testing above

SELECT
       a.[respondent_name]
     , b.report_prd
     , COUNT(b.report_prd)          AS Cnt
     , SUM(b.report_prd)            AS Cnt_by_Sum
FROM [eqr_ident_attsttn$] a
INNER JOIN (
             -- nb: do NOT just use UNION in this query, UNION ALL is required.

             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201212_al]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]
                       
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201212_mz]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201209_al]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201209_mz]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201209_388]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201206_al]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201206_mz]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201206_388]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201203_al]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]
             
             UNION ALL
             
             SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201203_mz]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]

             ) b ON a.respondent_id = b.respondent_id

--WHERE b.report_yr LIKE '2012' -- assuming this comes from alias a
--AND b.report_prd like 12

GROUP BY
       a.[respondent_name]
     , b.report_prd

Open in new window

0
 
jimmylew52Author Commented:
SELECT respondent_id, [column 2] as report_prd, count(*) as report_prd_count
             FROM [trans_201212_al]
             WHERE [column 1] like '2012%'
             GROUP BY respondent_id, [column 2]

Works with out problems and the data looks good. Everything works but the last script.

This is the error I get.

Msg 8117, Level 16, State 1, Line 5
Operand data type varchar is invalid for sum operator.
0
 
PortletPaulfreelancerCommented:
>>Msg 8117, Level 16, State 1, Line 5
>>Operand data type varchar is invalid for sum operator.

Line 5 has a slip-up, this is what it currently is:
     , SUM(b.report_prd)            AS Cnt_by_Sum

The message identifies the line and the problem, the field report_prd is varchar (and you can't sum varchar data). The message is designed to help you make corrections :)

This is what that line should be:
     , SUM(b.report_prd_count)            AS Cnt_by_Sum

report_prd_count is an integer and can be summed.

not sure how I introduced that error, sorry.
0
 
jimmylew52Author Commented:
Looks great!!!!!!!

I submitted the results and told them you were responsible for the query as I did not want them to think I was capable of anything this complicated in the future.

Could i impose on you to tell me how to get a total for each respondent_id?  I made a couple of attempts but was unsuccessful in modifying your query to get the the totals.

I can ask it as a separate question if you would rather.
0
 
PortletPaulfreelancerCommented:
>>how to get a total for each respondent_id?
 is already "per respondent"

All you would have to do it include it,  in 2 places


SELECT
       a.[respondent_name]
     , a.respondent_id
....
....
GROUP BY
       a.[respondent_name]
     , a.respondent_id


If this isn't what you are after - I would suggest another question
0
 
jimmylew52Author Commented:
That is exactly what i was looking for.  Thank you so much for hanging in there with me and getting ths taken care of.
0
 
jimmylew52Author Commented:
Excellent solution and very patient with a difficult problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 18
  • 12
  • 8
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now