15,501,490 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

Top Experts
Last 24hrsThis month
 OriginalGriff 287 CPallini 140 Dave Kreskowiak 60 Richard MacCutchan 40 George Swan 25
 OriginalGriff 3,897 Richard MacCutchan 2,049 Richard Deeming 1,523 Graeme_Grant 1,401 CPallini 1,313

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900