Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS Sql Query

Posted on 2009-04-06
5
191 Views
Last Modified: 2012-05-06
I have a table called RestChist and need to pull the following feilds:
CK_CHECKNO, CK_STATUS, CK_COVERS,  CK_TIP,  CK_DISCOUNT,  CK_FOOD,  CK_BEVG,  CK_BEER,  CK_WINE,  CK_LIQUOR, CK_OTHER , CK_TAX  and RESTNO

But, I need to do a distinct count on the CK_CHECKNO to give me a count of how many checks by the server
and
Sum CK_FOOD+CK_BEVG+CK_BEER+CK_WINE+CK_LIQUOR+CK_OTHER as "Items Sold"
and
sum the "items Sold" along with tips, tax minus discounts to get "total"

What I need is to get something like the following

Server         No. of Checks         Items Sold        Tax          Tips       Discounts        Total
1                       10                          $100              $10          $15           $5                  $120
2                        5                           $75                $7.50        $20           $0                 $102.50


Thanks

0
Comment
Question by:huntleyj
  • 3
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 24081274
Defining a problem is half of the solution and you have defined it well.
Look at the following and see if it gives you what you want:

SELECT SERVER, 
       COUNT(CK_CHECKNO),
       SUM(CK_FOOD+CK_BEVG+CK_BEER+CK_WINE+CK_LIQUOR+CK_OTHER) AS ItemsSolds,
       SUM(CK_TAX) AS Tax,
       SUM(CK_TIP) AS Tips,
       SUM(CK_DISCOUNT) AS Discounts,
       SUM(CK_FOOD+CK_BEVG+CK_BEER+CK_WINE+CK_LIQUOR+CK_OTHER + CK_TAX + CK_TIP - CK-DISCOUNT) AS Total 
FROM RestChist 
GROUP BY Server

Open in new window

0
 
LVL 3

Author Comment

by:huntleyj
ID: 24081577
Looks like it did the trick. Only I forgot to add that I needed it for a specific date but I should be able to add in that without issue.
0
 
LVL 3

Author Closing Comment

by:huntleyj
ID: 31567211
Worked great
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24081676
If you need it for a specific date, add that as a WHERE clause between lines 8 and 9.  If you need it for more than one date (as in , "by day for a period between mm/dd/yyyy and mm/dd/yyyy", then include the DateClumn in the SELECT right after the SERVER and group by both the SERVER and your datecolumn with the WHERE clause (between the current lines 8 and 9) indicating WHERE yourdatecolumn BETWEEN date1 AND date2.
0
 
LVL 3

Author Comment

by:huntleyj
ID: 24082604
thanks again.
I did the where clause and got the info I think I need. Now I'll take it to the "powers that be" and see if it suits them.
Good to know about the second grouping option incase they want the info that way.
 
Cheers
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pivot not using aggregate yield error 3 61
Error in SSIS while executing  - Potential data loss 4 31
SSRS 2016 KPI 2 32
SSRS Enable Remote Errors 4 34
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question