15,123,367 members
1.00/5 (1 vote)
See more:
Hello

I have this sql selects with a UNION but i need the result in two columns, normal and urgent not the result in the same column

```SELECT COUNT(k.Id) AS 'Normal' , P.Descr, P.DthUlt, P.Idpain, E.IP, E.IdPan, K.IdEst, k.Idpain from Kit AS K

INNER JOIN  Extends AS E ON E.IdPan = K.IdPain
INNER JOIN Painel AS P ON P.IdPain = k.IdPain and k.IdEst = 1

GROUP BY k.IdPain, P.Descr, P.DthUlt, P.Idpain, E.IP, E.IdPan, K.IdEst

UNION

SELECT COUNT(k.Id) AS 'Urgent' , P.Descr, P.DthUlt, P.Idpain, E.IP, E.IdPan, K.IdEst, k.Idpain from Kit AS K

INNER JOIN  Extends AS E ON E.IdPan = K.IdPain
INNER JOIN Painel AS P ON P.IdPain = k.IdPain and k.IdEst = 2

GROUP BY k.IdPain, P.Descr, P.DthUlt, P.Idpain, E.IP, E.IdPan, K.IdEst

order by P.Descr```

¿How i can add a new column named normal and other named urgent with the counts?

edit: im loking to improve it in an only one row

What I have tried:

Two selects, inner join, unión all..
Posted
Updated 26-Mar-18 8:37am
v2

## Solution 1

Include a "dummy" column in each select e.g.

```SELECT COUNT(k.Id) AS 'Normal', 0 AS 'Urgent' , P.Descr, P.DthUlt, P.Idpain, E.IP, E.IdPan, K.IdEst, k.Idpain from Kit AS K

INNER JOIN  Extends AS E ON E.IdPan = K.IdPain
INNER JOIN Painel AS P ON P.IdPain = k.IdPain and k.IdEst = 1

GROUP BY k.IdPain, P.Descr, P.DthUlt, P.Idpain, E.IP, E.IdPan, K.IdEst

UNION

SELECT 0 AS 'Normal', COUNT(k.Id) AS 'Urgent' , P.Descr, P.DthUlt, P.Idpain, E.IP, E.IdPan, K.IdEst, k.Idpain from Kit AS K

INNER JOIN  Extends AS E ON E.IdPan = K.IdPain
INNER JOIN Painel AS P ON P.IdPain = k.IdPain and k.IdEst = 2

GROUP BY k.IdPain, P.Descr, P.DthUlt, P.Idpain, E.IP, E.IdPan, K.IdEst

order by P.Descr```

Your next problem is that this will be over 2 rows and not one, but without sample and expected data I'm not going to dig any further. You may need to use a CASE WHEN ...END or a PARTITION statement. Depends on what version of SQL you are using

[EDIT] Here is a brute force method for getting your query onto 1 line per group:
SQL
```select SUM(normal) as 'Normal', SUM(Urgent) as 'Urgent', Descr, DtUlt, Idpain, IP, IdPan, IdEst
from (

-- insert the query above into here

) q
group by Descr, DtUlt, Idpain, IP, IdPan, IdEst```

You can probably simplify the original query by using a PARTITION rather than the UNION but the solution I've presented here will work even though it is not elegant
v3
Maciej Los 26-Mar-18 11:30am

5ed!
drvfn 26-Mar-18 13:28pm

Thank you CHill60, your solution Works perfect but as you said i need this in one row,
this is for a datagridvies in a Windows form application , make this will be very helpfull for me, i tryed to use case when end but i didnt find how,
CHill60 26-Mar-18 13:53pm

As I suggested, post some sample data and your expected results, tell us what version of sql you are using and we may be able to help
drvfn 26-Mar-18 15:20pm

thank you for your answer CHiLL60, the sql is 11.1 and my expected results are like:

Normal / Urgent / Descr / DthUlt / P.Idpain / IP / E.IdPan / k.Idpain
12 / 10 / tuve / 02-02-18 / 12 /10.18.1.1 / 12 / 12
0 / 1 / tuve2/ 02-02-18 / 13 /10.18.1.2 / 13 / 13
14 / 0 / tuve3 / 02-02-18 / 15 /10.18.1.8 / 15 / 15
0 / 0 / tuve4/ 02-02-18 / 17 /10.18.1.9 / 17 / 17

with your last solution i have:

Normal / Urgent / Descr / DthUlt / P.Idpain / IP / E.IdPan / k.Idpain
12 / 0 / tuve / 02-02-18 / 12 /10.18.1.1 / 12 / 12
0 / 10 / tuve / 02-02-18 / 12 /10.18.1.1 / 12 / 12
14 / 0 / tuve3 / 02-02-18 / 15 /10.18.1.8 / 15 / 15
0 / 0 / tuve3 / 02-02-18 / 15 /10.18.1.8 / 15 / 15

i hope this will be helpfull four undestand it
CHill60 27-Mar-18 5:59am

I've updated my solution