1

Topic: Request about the total of an ode a line

Good afternoon. Help, please, about request.
There are two tables: the nomenclature and requests.
The nomenclature:
nom_id | nom_type
1 | salon
2 | a luggage carrier
Requests:
req_num | req_qty | nom_id
25 | 5 | 1
25 | 3 | 2
As to me to receive the total single line (but in two columns) under the request table that in one column there was a total of salons, in other column the total of luggage carriers under one request.
Thanks.

2

Re: Request about the total of an ode a line

gigar;
PIVOT, CASE, well and is more correct dynamic PIVOT

3

Re: Request about the total of an ode a line

Tried so
select request.request_num;
(select SUM (request.request_qty) where nomenclature.nomenclature_type =' ') as s;
(select SUM (request.request_qty) where nomenclature.nomenclature_type =' ') as b
from request
inner join nomenclature
on request.nomenclature_id=nomenclature.nomenclature_id
group by request.request_num,nomenclature.nomenclature_type
But it is deduced for each request on two lines and how to make that in single line were deduced?

4

Re: Request about the total of an ode a line

Remove from grouping nomenclature.nomenclature_type

5

Re: Request about the total of an ode a line

gigar wrote:

Tried so
select request.request_num;
(select SUM (request.request_qty) where nomenclature.nomenclature_type =' ') as s;
(select SUM (request.request_qty) where nomenclature.nomenclature_type =' ') as b
from request
inner join nomenclature
on request.nomenclature_id=nomenclature.nomenclature_id
group by request.request_num,nomenclature.nomenclature_type
But it is deduced for each request on two lines and how to make that in single line were deduced?

It not a working script

6

Re: Request about the total of an ode a line

Deduces an error
The message 8120, level 16, state 1, line 3
The column "nomenclature.nomenclature_type" is inadmissible in the selection list as it does not contain neither in an aggregate function, nor in sentence GROUP BY.

7

Re: Request about the total of an ode a line

gigar;

DECLARE @nomenclature TABLE (nom_id INT, nom_type VARCHAR (255))
INSERT INTO @nomenclature (nom_id, nom_type)
VALUES (1, ' salon ');
(2, ' a luggage carrier ')
DECLARE @request TABLE (req_num INT, req_qty INT, nom_id INT)
INSERT INTO @request (req_num, req_qty, nom_id)
VALUES
(25, 5, 1);
(25, 3, 2)
SELECT
a.req_num;
SUM (CASE WHEN b. nom_type = ' salon ' THEN req_qty ELSE 0 END) as s;
SUM (CASE WHEN b.nom_type = ' a luggage carrier ' THEN req_qty ELSE 0 END) as b
FROM
@request a
INNER JOIN
@nomenclature b
ON
a.nom_id = b.nom_id
GROUP BY
a.req_num

8

Re: Request about the total of an ode a line

SELECT req_num
,=SUM (CASE nom_id WHEN 1 THEN req_qty ELSE 0 END)
,=SUM (CASE nom_id WHEN 2 THEN req_qty ELSE 0 END)
FROM requests
GROUP BY req_num;

9

Re: Request about the total of an ode a line

Thanks. Now works as it is necessary.