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?
7 19843
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 -
SELECT issue,Count(issue) as total,
-
(
-
SELECT Count(issue) as total1 FROM mytable2
-
Where mytable2.issue = mytable.issue
-
),
-
(
-
SELECT Count(issue) as total2 FROM mytable3
-
Where mytable3.issue = mytable.issue
-
)
-
FROM mytable Group by mytable.issue
If my understanding of your problem is correct, you can do it the other way round: -
select issue, Count(issue) as total
-
from
-
(
-
select issue from mytable1
-
union
-
select issue from mytable2
-
union
-
select issue from mytable3
-
)
-
group by issue
-
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
Still no luck, I got a MS SQL server syntax error (near keywork group). - select issue, Count(issue) as total
-
-
from
-
-
(
-
select issue from mytable1
-
-
union
-
-
select issue from mytable2
-
-
union
-
-
select issue from mytable3
-
)
-
group by issue
Any other suggestions? I know it has to be possible.
Still no luck, I got a MS SQL server syntax error (near keywork group). - select issue, Count(issue) as total
-
-
from
-
-
(
-
select issue from mytable1
-
-
union
-
-
select issue from mytable2
-
-
union
-
-
select issue from mytable3
-
)
-
group by issue
Any other suggestions? I know it has to be possible.
just try Group By myTable1.issue
Still no luck, I got a MS SQL server syntax error (near keywork group). - select issue, Count(issue) as total
-
-
from
-
-
(
-
select issue from mytable1
-
-
union
-
-
select issue from mytable2
-
-
union
-
-
select issue from mytable3
-
)
-
group by issue
Any other suggestions? I know it has to be possible.
Sorry, my fault, now I copy it from Query Analyzer (:-)): -
select x.issue, count(x.issue)
-
from (
-
select issue from mytable1
-
union all
-
select issue from mytable2
-
union all
-
select issue from mytable3
-
) as x
-
group by x.issue
-
Thank you. have not tried it yet but I have a good feeling about that query because I can see the logic. :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
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!...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |