473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

use count & group by with union

sharijl
I have a SQL query which returns the total amount of issues:

[HTML]SELECT issue,Count(issue) as total FROM mytable Group by issue[/HTML]

This works but I need the total of the same catagory in three tables so I assumed a union would work so tried something like this:

[HTML]SELECT issue,Count(issue) as total FROM mytable Group by issue
Union
SELECT issue,Count(issue) as totalFROM mytable2 Group by issue
union
SELECT issue,Count(issue) as total FROM mytable3 Group by issue[/HTML]

the problem is that the issue are duplicated so I will not get the total count I am looking for.

current results:
[HTML]
issue total
headache 4
headache 30
headache 2
cough 5
cough 55
cough 5[/HTML]

wanted result:
[HTML]
issue total
headache 36
cough 65[/HTML]


I assumed I could simply add sum(total) but did not work. What is the correct way to do this?
Jul 31 '07 #1
7 19842
hariharanmca
1,977 1GB
I have a SQL query which returns the total amount of issues:

[HTML]SELECT issue,Count(issue) as total FROM mytable Group by issue[/HTML]

This works but I need the total of the same catagory in three tables so I assumed a union would work so tried something like this:

[HTML]SELECT issue,Count(issue) as total FROM mytable Group by issue
Union
SELECT issue,Count(issue) as totalFROM mytable2 Group by issue
union
SELECT issue,Count(issue) as total FROM mytable3 Group by issue[/HTML]

the problem is that the issue are duplicated so I will not get the total count I am looking for.

current results:
[HTML]
issue total
headache 4
headache 30
headache 2
cough 5
cough 55
cough 5[/HTML]

wanted result:
[HTML]
issue total
headache 36
cough 65[/HTML]


I assumed I could simply add sum(total) but did not work. What is the correct way to do this?

You can use like

Expand|Select|Wrap|Line Numbers
  1. SELECT issue,Count(issue) as total, 
  2. (
  3. SELECT Count(issue) as total1 FROM mytable2 
  4. Where mytable2.issue = mytable.issue
  5. ),
  6. (
  7. SELECT Count(issue) as total2 FROM mytable3
  8. Where mytable3.issue = mytable.issue
  9. )
  10.  FROM mytable Group by mytable.issue
Jul 31 '07 #2
azimmer
200 Expert 100+
If my understanding of your problem is correct, you can do it the other way round:
Expand|Select|Wrap|Line Numbers
  1. select issue, Count(issue) as total
  2. from
  3. (
  4. select issue from mytable1
  5. union
  6. select issue from mytable2
  7. union
  8. select issue from mytable3
  9. )
  10. group by issue
  11.  
Jul 31 '07 #3
I will try your suggestion azimmer and post the results. I really hope that will work because the other suggestions will not give the results I wanted. with this I have four columns displayed.

issue
total (from table one only)
unnamed( count from table 2)
unnamed (count from table 3)

I need to somehow compile total + unnamed + unnamed

Thanks all
Jul 31 '07 #4
Still no luck, I got a MS SQL server syntax error (near keywork group).

Expand|Select|Wrap|Line Numbers
  1. select issue, Count(issue) as total
  2.  
  3. from
  4.  
  5.       (
  6.      select issue from mytable1
  7.  
  8.       union
  9.  
  10.       select issue from mytable2
  11.  
  12.       union
  13.  
  14.       select issue from mytable3
  15.       )
  16.       group by issue
Any other suggestions? I know it has to be possible.
Aug 1 '07 #5
hariharanmca
1,977 1GB
Still no luck, I got a MS SQL server syntax error (near keywork group).

Expand|Select|Wrap|Line Numbers
  1. select issue, Count(issue) as total
  2.  
  3. from
  4.  
  5.       (
  6.      select issue from mytable1
  7.  
  8.       union
  9.  
  10.       select issue from mytable2
  11.  
  12.       union
  13.  
  14.       select issue from mytable3
  15.       )
  16.       group by issue
Any other suggestions? I know it has to be possible.
just try Group By myTable1.issue
Aug 1 '07 #6
azimmer
200 Expert 100+
Still no luck, I got a MS SQL server syntax error (near keywork group).

Expand|Select|Wrap|Line Numbers
  1. select issue, Count(issue) as total
  2.  
  3. from
  4.  
  5.       (
  6.      select issue from mytable1
  7.  
  8.       union
  9.  
  10.       select issue from mytable2
  11.  
  12.       union
  13.  
  14.       select issue from mytable3
  15.       )
  16.       group by issue
Any other suggestions? I know it has to be possible.
Sorry, my fault, now I copy it from Query Analyzer (:-)):

Expand|Select|Wrap|Line Numbers
  1. select x.issue, count(x.issue)
  2. from (
  3. select issue from mytable1
  4. union all
  5. select issue from mytable2
  6. union all
  7. select issue from mytable3
  8. ) as x
  9. group by x.issue
  10.  
Aug 1 '07 #7
Thank you. have not tried it yet but I have a good feeling about that query because I can see the logic. :-)
Aug 1 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Son KwonNam | last post by:
When I try <xsl:value-of select="count('/GROUPS/GROUP')"/> on JSTL <x:transform> tag, I got the following error message. org.apache.jasper.JasperException: Can not convert #STRING to a NodeList!...
3
by: Abhi | last post by:
Hi! I am wondering if this query is possible somehow: I have a table with many fields that all can have a value from 1 to 5. if I wanna see the count of each value from one field, then this...
2
by: Greg Strong | last post by:
Hello All, Is there a simpler way to count text boxes with data in them when the 4 text boxes are in the header of a form? I've written the code below and it works. ,----- | Private Sub...
1
by: sammy | last post by:
If you have a select with 2 attributes where you group by one attribute and do a count() for the second attribute, if the count() is 0 then that row is never displayed. How would you instead see...
0
by: RSN | last post by:
I hav a query that uses a union of 16 tables to get some counts and i need to get the end result as a Group by. This is inside a sub-query that has the count(*) ans the attribute for the group-by in...
10
by: marting | last post by:
Before I throw my new expensive laptop out of the window! I'm stuck on getting my joins right with the correct amount in a column count. After speaking with someone a few weeks back, they...
3
by: weirdguy | last post by:
Hello all, Before you read my question, I would advise you to visit the Image-Link. The image-link shows 4Tables - its field, Primary, Foreign Keys, Relationships. The bottom of the Image, it...
0
by: ryangsh | last post by:
I'm trying to consolidate inventory records in db. (I'm displaying the result in a table form but for this post I'm putting it simple) table_name= PC: department - msoffice IT - 2000 IT - 2002...
8
by: Amy Badgett | last post by:
The purpose of this query is to return seven different Counts for these 14 different EIDs (employee IDs). The problem is that only 8 of the EIDs are returned: 123, 122, 108, 164, 131, 136, 138, 150....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.