Solved

SQL query to counts records by field.

Posted on 2013-07-01
42
505 Views
Last Modified: 2013-07-07
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
Comment
Question by:jimmylew52
  • 18
  • 12
  • 8
  • +2
42 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39290977
Have you reversed your GROUP BY, so that it reads:

GROUP BY a.[respondent_name],report_prd

??
0
 
LVL 1

Author Comment

by:jimmylew52
ID: 39290987
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39291003
Change COUNT(report_prd) to COUNT(1)
0
 
LVL 1

Author Comment

by:jimmylew52
ID: 39291008
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39291014
Is 3, 6, 12 the value for Report_prd, or is that also a count you desire?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39291017
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
 
LVL 6

Expert Comment

by:Dulton
ID: 39291028
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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39291064
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39291078
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39291109
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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39291150
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
 
LVL 6

Expert Comment

by:Dulton
ID: 39291165
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39291166
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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39291531
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39291576
Is there a report_prd in the trans_XXXXXX_XX tables?

Pretty sure there is a Cartesian issue.
0
 
LVL 1

Author Comment

by:jimmylew52
ID: 39291584
Yes there is a  there a report_prd in the trans_XXXXXX_XX tables.

Sorry, don't know what a Cartesian issue is.
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39291597
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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39291622
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39291655
I thought you said report_prd was in those trans_XXXXXX_XX tables?
0
 
LVL 1

Author Comment

by:jimmylew52
ID: 39291663
The data is there but it is called column 2, sorry about that, this is a converted db.
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39291687
So have you joined that to report_prd? :)
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:jimmylew52
ID: 39291705
results look the same. :-(
0
 
LVL 1

Author Comment

by:jimmylew52
ID: 39291708
Joined??? as in Union all???

or

AND 'Column 2' = 'Column 2'
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39291860
Probably something like:

ON a.respondent_id = b.respondent_id AND a.report_prod = b.[Column 2]
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39292275
need revision - sorry
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39292341
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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39293270
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39293390
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39293483
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39293522
>>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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39293538
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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39293577
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39293578
Hi Jimmy. Can you post the trans_xxxxxx_xx table layout (the column names, not the header names)?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39293582
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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39293679
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39295319
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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39298407
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39298630
>>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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39304205
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39304743
>>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
 
LVL 1

Author Comment

by:jimmylew52
ID: 39305918
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
 
LVL 1

Author Closing Comment

by:jimmylew52
ID: 39305923
Excellent solution and very patient with a difficult problem.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now