Click here to Skip to main content
15,069,133 members
Please Sign up or sign in to vote.
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 7:37am
v2

1 solution

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
Comments
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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




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