How to combine these sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to combine these sql

Author

Topic

man889
Starting Member

25 Posts

Posted - 2011-04-16 : 22:03:17

SELECT Sum(JobItem.JobPrice) AS JobPrice_Type_1_Sum, JobItem.InvNo
FROM JobItem
WHERE (((JobItem.JobTypeId)=1))
GROUP BY JobItem.InvNo;

SELECT Sum(JobItem.JobPrice) AS JobPrice_Type_2_Sum, JobItem.InvNo
FROM JobItem
WHERE (((JobItem.JobTypeId)=2))
GROUP BY JobItem.InvNo;

SELECT Sum(JobItem.JobPrice) AS JobPrice_Type_3_Sum, JobItem.InvNo
FROM JobItem
WHERE (((JobItem.JobTypeId)=3))
GROUP BY JobItem.InvNo;

SELECT Sum(JobItem.JobPrice) AS JobPrice_Type_4_Sum, JobItem.InvNo
FROM JobItem
WHERE (((JobItem.JobTypeId)=4))
GROUP BY JobItem.InvNo;

man889
Starting Member

25 Posts

Posted - 2011-04-16 : 22:26:18

Can I combine it into one table by InvNo and
having the column of InvNo, JobPrice_Type_1_Sum, JobPrice_Type_2_Sum, JobPrice_Type_3_Sum, JobPrice_Type_4_Sum

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-04-18 : 05:10:02

Have a look at the CASE statement

select invno,
case when JobTypeId = 1 then JobPrice
else 0
end as job1_raw,
case when JobTypeId = 2 then JobPrice
else 0
end as job2_raw,
etc
from jobitem

wrap the results inside a "group by" clause. Have a go, experiment and revert here with any problems.

man889
Starting Member

25 Posts

Posted - 2011-04-18 : 05:40:20

Hi AndrewMurphy,
Thank for your reply.
I figure out what I am look for is transform pivot after spend several hours searching.

and now I can get the result from ms access

TRANSFORM Sum(JobItem.JobPrice) AS JobPriceOfSum
SELECT JobItem.InvNo, Sum(JobItem.JobPrice) AS [Total Of JobPrice]
FROM JobItem
GROUP BY JobItem.InvNo
PIVOT JobItem.JobTypeId;

Here is the table and sample data
JobId, JobItemNo, InvNo, JobTypeId, JobPrice
1, 1, 3401, 1, 50
2, 2, 3403, 1, 60
3, 3, 3401, 2, 560
4, 4, 3402, 3, 10
5, 5, 3401, 1, 170
6, 1, 3402, 4, 140
7, 2, 3402, 3, 140
8, 1, 3403, 1, 300
9, 1, 3404, 2, 300
10, 1, 3405, 1, 300
11, 1, 3406, 1, 340
12, 1, 3407, 1, 300
13, 1, 3408, 1, 300

InvNo, Total Of JobPrice, JobTypeId 1, JobTypeId 2, JobTypeId 3, JobTypeId4
3418, 1090, 1080, 0, 10, 0
3419, 300, 300, 0, 0, 0
3420, 350, 300, 0, 0, 50
3421, 736 , 736 , 0, 0, 0
3422, 300, 300, 0, 0, 0
3429, 3400, 1150, 2250, 0, 0
3430, 2200, 0, 0, 2200, 0
3431, 2200, 0, 2200, 0, 0
3432, 2500, 0, 2500, 0, 0
3433, 1950, 0, 1950, 0, 0

but Now I am facing another problem is actually I will run the sql in VB.NET, the problem is VB.NET not allow using Transform pivot.

How can I convert it into standard sql statement?

Thank

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-04-18 : 07:22:17

I'm not familiar with vb.net, but the SQL I provided "is standard" and will point you in the right direction. Have a go and post your efforts for others to observe and fine-tune.

SMerrill
Posting Yak Master

206 Posts

Posted - 2011-04-29 : 19:10:29

Are you using ADO.NET to connect with Access?

~ Shaun Merrill
Seattle area

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-29 : 19:27:38

SELECT JobItem.InvNo,
Sum(case when JobItem.JobTypeId)=1 then JobItem.JobPrice else 0 end) AS JobPrice_Type_1_Sum,
Sum(case when JobItem.JobTypeId)=2 then JobItem.JobPrice else 0 end) AS JobPrice_Type_2_Sum,
...
FROM JobItem
GROUP BY JobItem.InvNo;

I think in access it might be something like
SELECT JobItem.InvNo,
Sum(iif (JobItem.JobTypeId)=1 , JobItem.JobPrice , 0) ) AS JobPrice_Type_1_Sum,
Sum(iif ( JobItem.JobTypeId)=2, JobItem.JobPrice, 0) ) AS JobPrice_Type_2_Sum,
...
FROM JobItem
GROUP BY JobItem.InvNo;

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources